Count If January AND "G"

[gri96]

I've drawn a blank.

Column A = Dates (DD/MM/YYYY)
Column M = RAG Status ("R" or "A" or "G")

I need to count up all of the R's in January, all of the A's in January, and all of the G's in January.
And so on for the other months.

I've got

=SUMPRODUCT( --ISNUMBER(Table1[Monthly schedule - Planned Date]), --(MONTH(Table1[Monthly schedule - Planned Date]) = 1))

Which returns a count of all of the 'January' 's.

I've also got

=COUNTIF(Table1[RAG STATUS],"G")

Which returns all of the 'G' 's - but for the entire table.

I kinda need a combo of the two?
I was playing with

=COUNTIFS(Table1[Monthly schedule - Planned Date], SUMPRODUCT( --ISNUMBER(Table1[Monthly schedule - Planned Date]), --(MONTH(Table1[Monthly schedule - Planned Date]) = 1)),Table1[RAG STATUS],"G")

But that isn't quite hitting it... Any advice is appreciated!!

 

[Misca]

Welcome to the board!

Your last try was so close. You were just thinking too complicated. The syntax is =COUNTIFS (range1, criteria1, [range2], [criteria2], ...) so all your need to do is give it a range and then the criteria:

=COUNTIFS(Month Range, Month, Letter Range, Letter)

Read a full tutorial.

 

[gri96]

Thanks for the swift reply!
So I tried that

=COUNTIFS(Table1[Monthly schedule - Planned Date], "JANUARY", Table1[RAG STATUS], "G")

with and without quotations, but it returns 0 ?
OR... Am I being really dense now

 

[gri96]

Turns out I needed SUMPRODUCT!

=SUMPRODUCT( --(MONTH(Table1[Monthly schedule - Planned Date]) = 1)*(Table1[RAG STATUS]="G"))

Seems to have worked!

 

[Joe4]

Try this formula for "R"

=SUMPRODUCT(--(MONTH(Table1[Monthly schedule - Planned Date])=1),--(Table1[RAG STATUS]="R"))

and change for other codes.


EDIT: See you just figured this out and posted!
Yes, because you need to apply the month function to your entire range, you need to use SUMPRODUCT instead of COUNTIFS.

 

[Misca]

Sorry. You are right: You can't use MONTH() with COUNTIFS range where as SUMPRODUCT has no problem doing so.

 

[gri96]

Thanks all!

 

[Joe4]

You are welcome.
Glad we were able to help (and happy to see that you were also able to come up with a solution on your own!).