Cell Counting 1: COUNTIF
One of the most useful and versatile builtin functions in OOo Calc, with a bit of creativity, you can use for a slew of tasks…
How many cells in a given range are negative.
=COUNTIF(A1:A20;”<0")
How many cells have the string “Yes”.
=COUNTIF(A1:A20;”Yes”)
Don’t forget to include the quotation marks around the condition argurment. Otherwise, you get an Err:510
Stefano Lenzi commented if you want that your criteria contains reference to other cells you can refer to the following example.
To count the cells less then A3:
=COUNTIF(”A1:A20″;CONCATENATE(”<";A3))
September 9th, 2007 at 3:49 am
Hi,
countif shows only 0 in my sample !?
=COUNTIF(C4:C85;D93)
where Col C is a list of names, where 1 name can apear more than 1 time. There are also empty cells.
I also tried with filling empry cells with ‘A’ but no change !
Only when I hard-code the name in the formular like this:
=COUNTIF(C4:C85;”gongyou”)
I get a correct result !? I checked data-type, I format the lookup-colum D explicitly text, but nothing works !
what’s wrong here ?
cheers LaoDe
September 9th, 2007 at 4:03 am
Hi,
I also would like to have a function, I would call ’sumif()’, doing almost the same like countif, but sum another column in the same range as I looked up.
Sample:
column A contains names, B numbers:
A B
—————-
Miller 5
Miller 8
Miller 3
Brown 6
Brown 7
Brown 4
so I’d like to do this:
sumif(A1:A6;”Miller”;B) but in a way, that automatic sum will be taken from B1:B3 ?
any ideas ? thanks LaoDe