countif dd/mmm/yy = Jan plus 2 more conditions

[WISEKATTY]

Hi, I am not sure how to count the month only for a countifs function.

What I need is if B:B = JAN and E:E = AY28 and F:F = AY12 then count as 1. (format of column B:B is dd/mmm/yy)

Examples I have tried:


Didn't limit to Jan only - the rest worked though.

=COUNTIF(B2:B500,"<=01/01/2013")-COUNTIF(B2:B500,">01/31/2013")+COUNTIFS(E:E,A16,F:F,AY28)


Found this example in another thread - Result is a #value ERROR - not sure if I am on the right track as I can't see a result.

=SUMPRODUCT(--(MONTH(B2:B500)=1)+(E:E)=AY16)+(F:F)=AY28

[Fotis1991]

Hi

I think that your request is not clear. Do you want to count all the cells in column B, IF in column E(in same row) existS the string AY16 AND in column F existS the string AY28? If YES, try this.

=SUMPRODUCT((MONTH(B2:B100)=1)*(E2:E100="AY16")*(F2:F1000="AY28"))

[WISEKATTY]

Hello thank you for your prompt response. Yes that is what I wanted but it gives me a 0 result. I have just worked out a solution not ideal but quick and easy. I have just added an additional column and entered only the month. Now I can count if with no problems I don't understand the sumproduct and how that works. I really appreciate your time thank you again.

[Fotis1991]

This article may helps you to understand SUMPRODUCT.

http://www.exceluser.com/explore/sumproduct_11.htm

If you want, upload a small sample workbook to see.