Countif: others?

Hi: I'm reasonably new to Excel, and have a fairly basic question to check out:
I have been using the COUNTIF function to count up numbers of items in various categories in a column.
The formulae I have been using are like this:
=COUNTIF(F$3:F$201, "Red")

or where I've wanted to combine various comments
=SUM(COUNTIF(F$3:F$201,"Yellow")+COUNTIF(F$3:F$201,"Cream"))

I'm not sure what formulae to use to count up
1) the total number of entries in that column, so that I can make sure that I haven't missed some (without having to check manually!)

2) how to count up the values that do not match the other categories that I have specified in the COUNTIFs: this would be a value for finding how many 'other' entries there are in that column, without having to specify those values

you can count all minus those that you counted:

=COUNTA(F$3:F$201)-COUNTIF(F$3:F$201, "Red")- etc

or, even better to refer to those cells where you already sum it (from your example above):

=COUNTA(F$3:F$201)-B2

ansar, welcome to the board...

Originally Posted by ansar

or where I've wanted to combine various comments
=SUM(COUNTIF(F$3:F$201,"Yellow")+COUNTIF(F$3:F$201,"Cream"))

FYI you can also write the above as:

=SUM(COUNTIF(F$3:F$201,{"Yellow","Cream"}))

useful if you have lots to add together as it prevents the formula from becoming too unwieldy

try this (adjust ranges as you like)

=SUMPRODUCT(--(D1:D999="red"),--(F1:F999="rough"))

You can also add more conditions

Have you thought of possibly using a Pivot Table(s) for your analysis ? See link in Sig. for general intro.

Use one of the fields you used for ROW Field again, you can use a field more than once, once in the DATA Field it's likely to default to Count (if non-numeric data field) - if not simply double click on the field and set to Count rather than Sum.

If in doubt post a sample to reflect where you are and we can show you the missing pieces.

Is this same problem or new (for new thread)?