What is wrong with this COUNTIFS formula?

Christina S.

=COUNTIFS({Consumer Alerts Sign-up Form Date}, >=DATE(IF(MONTH(TODAY())>=7, YEAR(TODAY()), YEAR(TODAY())-1), 7, 1), {Consumer Alerts Sign-up Form Date}, <=DATE(IF(MONTH(TODAY())>=7, YEAR(TODAY())+1, YEAR(TODAY())), 7, 31), {Consumer Alerts Sign-up Form Range 1}, @cell = "")

I am trying to create a COUNTIFS formula for each month that updates automatically each year. Our fiscal year begins in July and ends in June. It is calculating all sign-ups for the fiscal year instead of only counting the number of new sign-ups for the month of July. Once I figure out how to get this to work I'll update the formula for each of the remaining months. I hope that makes sense.

Any help would be greatly appreciated.

Thank you!

Leibel S

@Christina S.

The year calculation in your formula should be the same for both criteria:

=COUNTIFS({Consumer Alerts Sign-up Form Date}, >=DATE(IF(MONTH(TODAY())>=7, YEAR(TODAY()), YEAR(TODAY())-1), 7, 1), {Consumer Alerts Sign-up Form Date}, <=DATE(IF(MONTH(TODAY())>=7, YEAR(TODAY()), YEAR(TODAY())-1), 7, 31), {Consumer Alerts Sign-up Form Range 1}, @cell = "")

Paul Newcome

Are you getting an error message or an incorrect count?

Leibel S

@Christina S.

The year calculation in your formula should be the same for both criteria:

=COUNTIFS({Consumer Alerts Sign-up Form Date}, >=DATE(IF(MONTH(TODAY())>=7, YEAR(TODAY()), YEAR(TODAY())-1), 7, 1), {Consumer Alerts Sign-up Form Date}, <=DATE(IF(MONTH(TODAY())>=7, YEAR(TODAY()), YEAR(TODAY())-1), 7, 31), {Consumer Alerts Sign-up Form Range 1}, @cell = "")

Christina S.

@Leibel S Thank you, that seems to have worked!

Christina S.

@Paul Newcome It was giving me an incorrect count. I was getting a count for the entire fiscal year instead of just the month of July.

Paul Newcome

I must have misread what you were trying to do. Another way (so you don't have to remember how many days are in each month and there is only one variable to change if you need to calculate for a different month instead of 4) would be to reference the month and year directly instead of using the DATE function.

COUNTIFS({Date Range}, AND(IFERROR(MONTH(@cell), 0) = 7, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) - IF(MONTH(TODAY())<7, 1, 0)), {Consumer Alerts Sign-up Form Range 1}, @cell = "")

Christina S.

@Paul Newcome Thank you! That works well.

However, when I plug in the formula for next month, June 2023, it is counting sign-ups from June 2022. I expected it to equal 0.

Paul Newcome

That is because the month of TODAY is less than 7. I may have misunderstood your logic. Does this work?

COUNTIFS({Date Range}, AND(IFERROR(MONTH(@cell), 0) = 7, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) + IF(MONTH(TODAY())>=7, 1, 0)), {Consumer Alerts Sign-up Form Range 1}, @cell = "")

Christina S.

Thank you, @Paul Newcome.

I was able to create two sets of formulas. One set for July through December and another set for January through June. The following seem to work.

July - December formulas s

=COUNTIFS({Consumer Alerts Sign-up Form Date}, AND(IFERROR(MONTH(@cell), 0) = 7, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) - IF(MONTH(TODAY())<=7, 1, 0)), {Consumer Alerts Sign-up Form Range 1}, @cell = "")

=COUNTIFS({Consumer Alerts Sign-up Form Date}, AND(IFERROR(MONTH(@cell), 0) = 8, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) - IF(MONTH(TODAY())<=7, 1, 0)), {Consumer Alerts Sign-up Form Range 1}, @cell = "")

=COUNTIFS({Consumer Alerts Sign-up Form Date}, AND(IFERROR(MONTH(@cell), 0) = 9, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) - IF(MONTH(TODAY())<=7, 1, 0)), {Consumer Alerts Sign-up Form Range 1}, @cell = "")

=COUNTIFS({Consumer Alerts Sign-up Form Date}, AND(IFERROR(MONTH(@cell), 0) = 10, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) - IF(MONTH(TODAY())<=7, 1, 0)), {Consumer Alerts Sign-up Form Range 1}, @cell = "")

=COUNTIFS({Consumer Alerts Sign-up Form Date}, AND(IFERROR(MONTH(@cell), 0) = 11, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) - IF(MONTH(TODAY())<=7, 1, 0)), {Consumer Alerts Sign-up Form Range 1}, @cell = "")

=COUNTIFS({Consumer Alerts Sign-up Form Date}, AND(IFERROR(MONTH(@cell), 0) = 12, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) - IF(MONTH(TODAY())<=7, 1, 0)), {Consumer Alerts Sign-up Form Range 1}, @cell = "")

January - June formulas

=COUNTIFS({Consumer Alerts Sign-up Form Date}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) + IF(MONTH(TODAY())>=7, 1, 0)), {Consumer Alerts Sign-up Form Range 1}, @cell = "")

=COUNTIFS({Consumer Alerts Sign-up Form Date}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) + IF(MONTH(TODAY())>=7, 1, 0)), {Consumer Alerts Sign-up Form Range 1}, @cell = "")

=COUNTIFS({Consumer Alerts Sign-up Form Date}, AND(IFERROR(MONTH(@cell), 0) = 3, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) + IF(MONTH(TODAY())>=7, 1, 0)), {Consumer Alerts Sign-up Form Range 1}, @cell = "")

=COUNTIFS({Consumer Alerts Sign-up Form Date}, AND(IFERROR(MONTH(@cell), 0) = 4, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) + IF(MONTH(TODAY())>=7, 1, 0)), {Consumer Alerts Sign-up Form Range 1}, @cell = "")

=COUNTIFS({Consumer Alerts Sign-up Form Date}, AND(IFERROR(MONTH(@cell), 0) = 5, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) + IF(MONTH(TODAY())>=7, 1, 0)), {Consumer Alerts Sign-up Form Range 1}, @cell = "")

=COUNTIFS({Consumer Alerts Sign-up Form Date}, AND(IFERROR(MONTH(@cell), 0) = 6, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) + IF(MONTH(TODAY())>=7, 1, 0)), {Consumer Alerts Sign-up Form Range 1}, @cell = "")