Try Able2Extract Professional Free
Counting Formulas
Formula Description
=COUNTA(A2:A6)
Counts the number of nonblank cells in a
range
=COUNTA(A2:A3, A6)
Counts the number of nonblank cells in the
top two, and bottom cells in a range
=COUNTIF(A2:A7,"valueX")
Counts number of entries for valueX amongst
values in a range of cells
=COUNTIF(A2:A7,A4)
Counts the number of entries within a
specific range for the value located in cell A4
=COUNTIF(B2:B7,">valueX")
Counts numbers in a certain range that are
greater than valueX
=COUNTIF(B2:B7,">="&B5)
Counts the number of values greater than or
equal to the value in cell B5
=COUNTIF(B2:B7,"< valueY")
Counts the number of values that are less
than valueY in a certain range
=COUNTIF(B2:B7,"<=valueX")
Counts the numbers that are less than or
equal to valueX in a certain range
=SUM(IF((A2:A7="value1")+(A2:A7="value2"
),1,0))
Counts the number of occurances for value1
or value2
=SUM(IF((B2:B7<valueX)+(B2:B7>valueY),1,0
))
Counts the number of occurances with values
less than valueX or greater than valueY
=SUM(IF(A2:A7="value1",IF(B2:B7<valueX,1,
0)))
Counts the number of occurances for value1
with a value less than valueX
=SUM(IF(FREQUENCY(A2:A10,A2:A10)>0,1))
Counts the number of unique number values
in a range, but does not count blank cells or
text values
=SUM(IF(FREQUENCY(MATCH(B2:B10,B2:B1
0,0))>0,1))
Counts the number of unique text and
number values in a range (which must not
contain blank cells)
=SUM(IF(FREQUENCY(IF(LEN(A2:A10)>0,MA
TCH(A2:A10,A2:A10,0),""),
IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),
""))>0,1))
Counts the number of unique text and
number values in cells A2:A10 , but does not
count blank cells or text values
