MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Count Function


Posted by Martin on October 24, 2001 6:44 AM

Can any one help me with the following, i'm sure the answer is simple but I can't get what i'm looking for:

I have an excel database/spreadsheet and I am trying to use the count if function with more than 1 column.

Column A has rows containing 1 of 5 different words(eg white black brown blue red) and column B has rows containing 1 of 5 different words(eg pink orange yellow gold silver).

I am producing a results table at the bottom of the sheet for which I need to count how many entries of each type in column B have a column A entry of each of the 5 options.
eg how many entries in column b have the word orange with a correspondin column a entry of red.

Any ideas


Posted by Aladin Akyurek on October 24, 2001 6:54 AM

=SUMPRODUCT((A1:A10="Red")*(B1:B10="orange"))

will give you a count of coocurrence involving red and orange.

Aladin

==========

Posted by Don C on October 24, 2001 7:20 AM

A pivot table would easily give the counts of all possible combinations without manually typing each possible value (as Aladin's formula would require).