Simple Formula Problem
Posted by Jason A on January 13, 2002 11:42 PM
Need help on a simple formual problem that I thought was simple.
I need to count the amount of entries in one column based in the criteria of a another column.
ie, if A1:A10 has various entries of "cat", "dog" and "fish", I want to count how many cells have an entry in B1:B10 with "cat" as the corresponding cell.
The entry in B1:B0 is not important as it varies.
Posted by Aladin Akyurek on January 14, 2002 12:51 AM
Jason --
It's:
=SUMPRODUCT((A1:A10="cat")*(B1:B10="cat"))
or, if E1 houses "cat",
=SUMPRODUCT((A1:A10=E1)*(B1:B10=E1))
Aladin
Posted by IML on January 14, 2002 4:27 AM
Slightly different read, you could also try something along the lines of:
=SUMPRODUCT((A1:A10="cat")*(B1:B10<>"")) or
=SUMPRODUCT((A1:A10="cat")*(ISNUMBER(B1:B10)))
If you want to count populated values in B1:B10 where a1:a10 equals cat.
good luck
Posted by Aladin Akyurek on January 14, 2002 5:58 AM
I see your reading is possible. But then,
=COUNTIF(A1:A10,"cat")
would be sufficient (where a corresponding blank B-value is also counted.
Aladin
Posted by IML on January 14, 2002 7:22 AM
agreed, I was just thinking of a data set like
{"cat",1;"dog",5;"cat",0;"dog",4;"cat",3;"cat",0;"dog",7;"dog",4;"dog",1;"bird",7}
where the 0's by cats are actually blanks or nulls (f9 populated them with a zero).
