Difference between revisions of "Manuals/calci/COUNTIF"

Latest revision as of 16:49, 7 August 2018

COUNTIF (Array,Condition)

'Array' is the range of cells.
'Condition' is the criteria in the form of a number, expression, cell reference, or text.
- COUNTIF(), counts the number of non blank cells within a range that meet the given criteria.

Description

COUNTIF (Array,Condition)
COUNTIF(1..1000,">500") returns 500 as a result.
COUNTIF(1..1000,"<=10") returns 10 as a result.
Considering an example in a spreadsheet, COUNTIF(A1:A8,"JOHN") returns the number of cells in the range A1 through A8 that match with the text "JOHN".
Criteria containing numbers can be expressed as 66, "66","<=66", ">=66", "<>66" etc.
Criteria with text values can be expressed in quotes as "Red" etc.
Combination of functions can also be used as COUNTIF(A1:A3,"Red")+COUNTIF(A4:A7,"Orange") etc.
COUNT() can be used to count the number of cells in the given range except logical values, error values and text.
COUNTA() can be used to count the number of non-empty cells that may be numbers, number representation of a text, error or logic values, dates etc.

ZOS

The syntax is to use COUNTIF function in ZOS is .
- $Condition$ is the criteria in the form of a number, expression, cell reference, or text.
For e.g.,COUNTIF(200..250,"<210")

Examples

Consider the following table which shows the color of object in first column and respective count in the second column.

COUNTIF() can be used to count the number of cells that match a given criteria within a mentioned range of values.

COLOR	QUANTITY
PURPLE	8
RED	6
BLUE	20
GREEN	23
RED	1
ORANGE	10
BROWN	2

=COUNTIF(A2:A8,"RED") : Counts the number of cells in the range A2 to A8 that has text "RED", returns 2 as a result.

=COUNTIF(B3:B8, ">8") : Counts the number of cells in the range B3 to B8 containing number of objects greater than '8', returns 3 as a result.

=COUNTIF(A3:A7,A4) : Counts the number of cells in the range A3 to A7, that has text similar to A4, returns 1 as a result.

=COUNTIF(B2:B6, "<>"&B4) : Counts the number of cells in the range B2 to B6 containing number of objects that are not equal to '20', returns 4 as a result.

=COUNTIF(B2:B8,">4") - COUNTIF(B2:B8,">20") : Returns the difference in number of cells in the range B2 to B8 with number>4 and number>20, returns 4 as a result.

References

List of Main Z Functions

Z3 home

@@ Line 1: / Line 1: @@
-=COUNTIF(r, c)=
+<div style="font-size:30px">'''COUNTIF (Array,Condition) '''</div><br/>
+*'Array' is the range of cells.
-*Where 'r' is the range of cells.
+*'Condition' is the criteria in the form of a number, expression, cell reference, or text.
-*'c' is the criteria in the form of a number, expression, cell reference, or text.
+**COUNTIF(), counts the number of non blank cells within a range that meet the given criteria.
-*COUNTIF() function calculates the number of cells that satisfy a given criteria.
 == Description ==
-COUNTIF(r, c)
-For example, COUNTIF(A1:A8,"JOHN") returns the number of cells in the range A1 through A8 that match with the text "JOHN".
+*COUNTIF (Array,Condition)
+*COUNTIF(1..1000,">500") ''returns 500'' as a result.
+*COUNTIF(1..1000,"<=10") ''returns 10'' as a result.
+*Considering an example in a spreadsheet, COUNTIF(A1:A8,"JOHN") returns the number of cells in the range A1 through A8 that match with the text "JOHN".
 *Criteria containing numbers can be expressed as 66, "66","<=66", ">=66", "<>66" etc.
 *Criteria with text values can be expressed in quotes as "Red" etc.
+*Combination of functions can also be used as COUNTIF(A1:A3,"Red")+COUNTIF(A4:A7,"Orange") etc.
+*COUNT() can be used to count the number of cells in the given range except logical values, error values and text.
+*COUNTA() can be used to count the number of non-empty cells that may be numbers, number representation of a text, error or logic values, dates etc.
-*Combination of functions can also be used as COUNTIF(A1:A3,"Red")+COUNTIF(A4:A7,"Orange") etc.
+==ZOS==
-*Special characters such as '*', '?', '~' can be used in the criteria field to match a certain sequence of characters.
+*The syntax is to use COUNTIF function in ZOS is <math>COUNTIF(Array,Condition)</math>.
+**<math>Condition</math> is the criteria in the form of a number, expression, cell reference, or text.
+*For e.g.,COUNTIF(200..250,"<210")
-*COUNT() can be used to count the number of cells in the given range except logical values, error values and text.
-*COUNTA() can be used to count the number of non-empty cells that may be numbers, number representation of a text, error or logic values, dates etc.
+== Examples  ==
-== Examples ==
 Consider the following table which shows the color of object in first column and respective count in the second column.
 COUNTIF() can be used to count the number of cells that match a given criteria within a mentioned range of values.
 <div id="2SpaceContent" class="zcontent" align="left">
@@ Line 30: / Line 34: @@
 {| id="TABLE3" class="SpreadSheet blue"
 |- class="even"
-|
+| class="sshl_f" | '''COLOR'''
-|
+| class="ssh1_f" | '''QUANTITY'''
 |- class="odd"
-| class="sshl_f " | PURPLE
+| class="sshl_f" | PURPLE
-| class="sshl_f " | 8
+| class="sshl_f" | 8
 |- class="even"
 | class="sshl_f" | RED
@@ Line 42: / Line 46: @@
 | class="sshl_f" | 20
 |- class="even"
-| class=" " | GREEN
+| class="sshl_f" | GREEN
-| class=" " | 23
+| class="sshl_f" | 23
 |- class="odd"
-| class="   " | RED
+| class="sshl_f" | RED
-| class=" " |1
+| class="sshl_f" |1
 |- class="even"
-| class=" " | ORANGE
+| class="sshl_f" | ORANGE
-| class=" " | 10
+| class="sshl_f" | 10
 |- class="odd"
-| class="   " | BROWN
+| class="sshl_f" | BROWN
-| class=" " |2
+| class="sshl_f" |2
 |}
 <div id="5SpaceContent" class="zcontent" align="left">
-=COUNTIF(A1:A7,"RED")   : Counts the number of cells in the range A1 to A7 that has text "RED", ''returns 2'' as a result.
-=COUNTIF(B2:B7, ">8")   : Counts the number of cells  in the range B2 to B7 containing number of objects greater than '8', ''returns 3'' as a result.
+=COUNTIF(A2:A8,"RED")   : Counts the number of cells in the range A2 to A8 that has text "RED", ''returns 2'' as a result.
+=COUNTIF(B3:B8, ">8")   : Counts the number of cells  in the range B3 to B8 containing number of objects greater than '8', ''returns 3'' as a result.
+=COUNTIF(A3:A7,A4)   : Counts the number of cells in the range A3 to A7, that has text similar to A4, ''returns 1'' as a result.
+=COUNTIF(B2:B6, "<>"&B4)   : Counts the number of cells  in the range B2 to B6 containing number of objects that are not equal to '20', ''returns 4'' as a result.
+=COUNTIF(B2:B8,">4") - COUNTIF(B2:B8,">20")   : Returns the difference in number of cells in the range B2 to B8 with number>4 and number>20, ''returns 4'' as a result.
-=COUNTIF((B1:B5),"A",11)   : Counts the number of cells that contain numbers in the cells B1 to B5 , text value "A" and number '11', ''returns 4'' as a result.
 </div>
+== Related Videos ==
+{{#ev:youtube|CLEYppjUWFI|280|center|COUNTIF}}
+== See Also ==
+*[[Manuals/calci/COUNT| COUNT]]
+*[[Manuals/calci/COUNTA | COUNTA]]
+==References==
+*[http://en.wikipedia.org/wiki/Aggregate_function List of Aggregate Functions]
+*[http://en.wikipedia.org/wiki/Aggregate_function COUNTIF]
+*[[Z_API_Functions | List of Main Z Functions]]
+*[[ Z3 |   Z3 home ]]

Difference between revisions of "Manuals/calci/COUNTIF"

Latest revision as of 16:49, 7 August 2018

Contents

Description

ZOS

Examples

Related Videos

See Also

References

Navigation menu

Search