Counting occurrences for each month for this year only

Erica Cole

I am using the formula below for a trend widget that pulls metrics for each month. This one is for January, but I have the same formula for each month, replacing 1 for january with 2 for february, etc. I need to update this formula so it counts if the month is january and the year is this year. I pulled one of my YTD formula from another sheet and threw a "hail mary" combining them. Any one know how to fix this?

Original working formula for the month of January:

=COUNTIFS({Sourcer}, "JJ", {Sourced Date}, IFERROR(MONTH(@cell ), 0) = 1)

My attempt to frankenstein a YTD version :/

=COUNTIFS({Sourcer}, "JJ", {Sourced Date}, IFFERROR (YEAR(@cell), 0) = YEAR(TODAY()), {Sourced Date}, IFERROR(MONTH(@cell ), 0) = 1)

Nick Korna

Hi @Erica Cole,

What error is your formula giving? Your formula should work (the month/year order doesn't matter) so either of the below should be valid:

=COUNTIFS({Sourcer}, "JJ", {Sourced Date}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()), {Sourced Date}, IFERROR(MONTH(@cell), 0) = 1)

=COUNTIFS({Sourcer}, "JJ", {Sourced Date}, IFERROR(MONTH(@cell), 0) = 1, {Sourced Date}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))

My initial thought was that the Sourced Date column hadn't been set to date, but this would stop the original formula from working. The only other thing I can think of is if the cross sheet references (if this is a different sheet) aren't set up quite right (one being a column and the other only a selection of cells?), but the error you're getting might shed some more light on this.

Nick Korna

Hi @Erica Cole,

What error is your formula giving? Your formula should work (the month/year order doesn't matter) so either of the below should be valid:

=COUNTIFS({Sourcer}, "JJ", {Sourced Date}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()), {Sourced Date}, IFERROR(MONTH(@cell), 0) = 1)

=COUNTIFS({Sourcer}, "JJ", {Sourced Date}, IFERROR(MONTH(@cell), 0) = 1, {Sourced Date}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))

My initial thought was that the Sourced Date column hadn't been set to date, but this would stop the original formula from working. The only other thing I can think of is if the cross sheet references (if this is a different sheet) aren't set up quite right (one being a column and the other only a selection of cells?), but the error you're getting might shed some more light on this.

Erica Cole

Hi Nick,

Thank you for responding. I'm getting #UNPARSEABLE with the first formula. But the second formula is working. I had tried it that way, but when it worked, I thought the count was off. Now that I'm manually checking the numbers, I think it's accurate. Thanks!