Using CountIfs for months and year

Hawk23

Hi,. I am struggling with my formula to CountIfs. Currently, I just have a COUNTIF formula in there to count from a reference sheet, the number of requests that came in by month only. I need to update this to be COUNTIFS though so that I can add in the year, so that I can see totals for each month/year vs just all of december for example. The numbers showing below for Nov/Dec are the total for both 2023 and 2024 but I need them to be distinct. Any thoughts?

below is a picture from my main tracking sheet:

Mark.poole

@Hawk23

Two things. I would recommend In your month submitted Column to Do this formula.

=IF(MONTH([Created Date]@row) = 1, "January", IF(MONTH([Created Date]@row) = 2, "February", IF(MONTH([Created Date]@row) = 3, "March", IF(MONTH([Created Date]@row) = 4, "April", IF(MONTH([Created Date]@row) = 5, "May", IF(MONTH([Created Date]@row) = 6, "June", IF(MONTH([Created Date]@row) = 7, "July", IF(MONTH([Created Date]@row) = 8, "August", IF(MONTH([Created Date]@row) = 9, "September", IF(MONTH([Created Date]@row) = 10, "October", IF(MONTH([Created Date]@row) = 11, "November", IF(MONTH([Created Date]@row) = 12, "December"))))))))))))

What this does is change the number to the month name. It will then make it so your count if formula can be dragged down instead of changing it for each row in a year.

Or Create a Helper column that is just =IF([Month Submitted]@row=1,"January"……..

You could also add a hidden "Helper column" to put the year in on the sheet your pulling data to. At which point You wont have to change the formula at all and just copy it to the rows you need.

That said Here is the formula you want.

=Countifs({Month Submitted Ref},[Month column]@row,{Year Submitted Ref},[Year Column]@row

otherwise

=Countifs({Month Submitted Ref},1,{Year Submitted Ref},2023)

=Countifs({Month Submitted Ref},2,{Year Submitted Ref},2023)

=Countifs({Month Submitted Ref},3,{Year Submitted Ref},2023)

=Countifs({Month Submitted Ref},1,{Year Submitted Ref},2024)

=Countifs({Month Submitted Ref},2,{Year Submitted Ref},2024)

=Countifs({Month Submitted Ref},3,{Year Submitted Ref},2024)

So on and so forth

Nick Korna

Hi @Hawk23,

If you have helper columns for year/month in your first sheet (the sheet for the totals), you don't need them in the data sheet and can use the Created column for both. For example, with a Month Number column and a Year Column in the first sheet you could then use a formula like this as a column formula:

=COUNTIFS({Created}, AND(IFERROR(MONTH(@cell) = [Month Number]@row, 0), IFERROR(YEAR(@cell) = Year@row, 0)))

Alternatively you could do a COUNTIFS without the helper columns, but you would then need to tailor each row. For example, Jan 2024 would be:

=COUNTIFS({Month Submitted},(IFERROR(MONTH(@cell) = 1), {Year Submitted},IFERROR(YEAR(@cell) = 2024))

This would entail a bit more manual work though - even a month number column alone would make it much easier to do.

Hope this helps, but if I've misunderstood anything or you've any problems/questions then just ask!