MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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).