Need help with countif with multiple criteria?

**Kaamakazii** · 01-20-2012, 01:50 PM

I have two collumns:
A B
YEAR MONTH
2006 June
2006 June
2007 Sept
2008 october
2006 January
2006 Februrary
2007 March
2008 March

On a separate sheet I have:
Year 2006
Month jan feb mar apr may jun jul aug sep oct nov
#instances ? ? ? ? ? ? ? ? ? ? ?

Now what function would grab only the 2006 year data and COUNT the number of Januarys?
***I would also like it to grab the entire collumn because the data sheet is constantly being updated.

Thank you soooo much!

**Richard Buttrey** · 01-20-2012, 02:00 PM

Hi
Have you tried =COUNTIF()
e.g.
=COUNTIFS(A:A,2006,B:B,"January")

If you change the horizontal list of abbreviated month names (assumed to be starting in B2) you could then use, (assuming 2006 is in say B1)

=COUNTIFS(A:A,B1,B:B,B$2)

**Kaamakazii** · 01-20-2012, 02:06 PM

=countif(Sheet1!$DE$2:$DE$13,"*" & I2 & "*") I2= aug

I got that to work but I had to manually select the 2006 data :/

I will try countifs and see if that helps

**dilipandey** · 01-20-2012, 02:07 PM

Hi Kaamakazi,

Welcome to the forum.

See the attachment where I have transferred your scenario and provided a solution.

Regards,
DILIPandey

<click on below 'star' if this helps>

**Kaamakazii** · 01-20-2012, 02:10 PM

I should have mentioned this before but I am using google docs.
It has many many many of the same functions. It has COUNTIF but not COUNTIFS...

**Kaamakazii** · 01-20-2012, 02:14 PM

Thank you dilipandey, but countifs doesn't work on goolge docs :/... You guys probably hate me now. Is there another way to do it with sumproduct or something?

**dilipandey** · 01-20-2012, 02:37 PM

Hi Kaamakazii,

Apply following formula in my attachment at g3 and drag it towards right.

{=SUM(--($A$2:$A$9=$G$1)*($B$2:$B$9=G$2))}

Note:- Above is an array formula and need to be confirmed with Ctrl Shift Enter.

Regards,
DILIPandey

<click on below 'star' if this helps>

**amycclark** · 01-20-2012, 02:57 PM

Will this work:

=SUMPRODUCT(($A$2:$A$9>=$D$1) * ($A$2:$A$9<=$D$2) * ($B$2:$B$9 = "January"))

Where D1 and D2 are your start and end dates? If you just want to calculate 2006 you would enter 2006 in both cells. There's probably a better way but this is the way I would do it.

And of course instead of writing "January" you could set up rows with each month in it and reference them.

I don't know anything about googledocs so maybe this is not an option.

Good luck!
~Amy