Countif: others?

[ansar]

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

[zbor]

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

[DonkeyOte]

ansar, welcome to the board...

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:

Please Login or Register  to view this content.

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

[ansar]

Thanks to both of you for your posts.....
will input those into the spreadsheet

I have another question......

I want to do a bit more analysis, so want to count up number of entries where I have, say "red" in column D and "rough" in column F.
I only want to count the "red" in column D if I have already satisfied the "rough" condition in column F

I have tried to use IF AND and LOOKUP.....

[zbor]

try this (adjust ranges as you like)

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

You can also add more conditions

[DonkeyOte]

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

[ansar]

Hi thanks for replies again!

Am having a go at the pivot table report, and have inputted row fields and column fields. Would now like program to count numbers for each of the intersecting fields.....
or.....how do i choose a count option for the 'drop data items here' bit of the table?

[DonkeyOte]

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.

[ansar]

thanks again: that's worked well.....

[ansar]

Hello again
I have 2 questions this time:
1) I want to do a simple analysis of the data, but this time by month.
My months have been inputted using the format eg:
26/01/2009
I want to do an analysis by month. Would I use COUNTIF (and if so, how do I input the criteria bit of the formula) or do I use a different formula?

2) I'm doing some similar work on data for different years:
Cutting and pasting of regular charts/ tables seems easy between worksheets: (updating Source Data etc seems easy enough with these charts).
I can't see a way to changing the source data for the pivot tables/ charts which I would like to copy over......
Could you give me some advice?

[zbor]

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

[ansar]

yes it's a new thread....will set it up as such!