Help with sheet summary formula for count if month/year

Eenmeier

I essentially need to do a summary count by month. I am trying to get a summary formula to count if the column equals a particular month/year combination. I have already created the needed helper formula to display the month and year combo. But the summary formula yields an Invalid Data Type error. Screenshot of the helper column below. I am trying to do a summary count by month and year. Count if Live Date Helper = 2023/01 (Jan 2023). And the next summary formula field would be Count If Live Date Helper = 2023/02 (Feb 2023). Here is the formula I am attempting for Jan: =COUNTIF([Live Date Helper]:[Live Date Helper], "2023/01"). If I remove the brackets I just get an unparseable error. Please help me figure out what I am doing wrong.

Nick Korna

What formula are you using to get the Live Helper Date?

If your dates are already in a date format then you could use something like this for January:

=COUNTIFS([Live Date]:[Live Date], IFERROR(MONTH(@cell), 0) = 1, [Live Date]:[Live Date], IFERROR(YEAR(@cell), 0) = 2023)

You can then just update the month/year numbers as applicable.

Alternatively, if you're wanting the data to feed into a report you could make another sheet with month/year columns and use cross sheet references and a single formula to get the counts instead.

If your live date isn't getting calculated using a base date, then some additional steps would be required, but it should be doable - let us know!

AravindGP

Hi @Eenmeier ,

It is probably to do with the column type where you are trying to apply the formula. Check if the column type is date. Your formula in itself is correct and it should work with the quotes for the value.

Eenmeier

Hi, thanks for the feedback. The column type was Text/Number. I just tried updating to it Date and I am yielding the same results. I am so lost on why its not working. Please let me know if you have other suggestions.

=COUNTIF([Live Date Helper]:[Live Date Helper], "2023/01") = Invalid

=COUNTIF(Live Date Helper:Live Date Helper, "2023/01") = Unparseable

Nick Korna

What formula are you using to get the Live Helper Date?

If your dates are already in a date format then you could use something like this for January:

=COUNTIFS([Live Date]:[Live Date], IFERROR(MONTH(@cell), 0) = 1, [Live Date]:[Live Date], IFERROR(YEAR(@cell), 0) = 2023)

You can then just update the month/year numbers as applicable.

Alternatively, if you're wanting the data to feed into a report you could make another sheet with month/year columns and use cross sheet references and a single formula to get the counts instead.

If your live date isn't getting calculated using a base date, then some additional steps would be required, but it should be doable - let us know!

Eenmeier

HI Nick. At first I got unparseable error but with a tweak it now works and eliminates the need for the helper column. The IFERROR was key here and what I forgot to include in my helper column. Thank you!