COUNTIF Function using more than one criterion

**ary** · 07-19-2007, 04:02 AM

I have created the following table in excel

Month/Yr Status
May-07 1
May-07 2
May-07 1
May-07 1
Jun-07 2
Jun-07 1
Jun-07 1

I now want to summarise this information in the following table

Month/Yr No. of "1" No. of "2"
May-07 x y
Jun-07 v z

I know that it is possible to count the number of cells which fulfil certain criteria by using the COUNTIF function. However I want to count the cells that have the entry "1" that are in May-07, then the cells that have the entry "1" that are in Jun-07. I currently have just used a COUNTIF function for each month (set the countif for each entry in the second table to look at specific cells), but I am trying to figure out how to just set one formula with two criteria (all cell in May that have the number "1") Of course the actual spreadsheet that I am using is much more complicated with hundreds of entries - this is just a simplified version of the problem.

Any suggestions?

Thanks!

**WinteE** · 07-19-2007, 04:14 AM

Proposing date in col. A and nrs. 1, 2 in col. B :

=SUMPRODUCT((A1:A10=Date)*(B1:B10=Nr.)*1)

To learn more about this function look at my site.

Erik

**ary** · 07-19-2007, 04:24 AM

I am not quite sure what you mean by that formula.

In the first table the dates and the numbers are in two separate columns, they got pushed out of alignment when I posted.

Maybe I wasn't specific enough in asking my question.

I want to a formula that will count the number of cells that contain the number "1" that are in may, then the number of cells that contain the number "1" that are in june and so on.

-ary
http://etesies.blogspot.com

**WinteE** · 07-19-2007, 04:40 AM

The formula would be like this :

=SUMPRODUCT((A1:A10="May-07")*(B1:B10=1)*1)

Instead of "May-07" I recommend to include a cell here were you enter it.

Erik