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