Countif This Month, Next Month, Following Month

JVaihinger

Heyo, 

I am trying to redo a formula to auto update with the information that I am currently pulling monthly for a dashboard. I am trying to sum the total fee installed if scheduled to complete this month, next month, and the following month. Here are the formulas I am using: 

This Month: =SUMIF({ECD}, IF(ISDATE(@cell), MONTH(@cell)) = MONTH(TODAY()), {$$})

Next Month: =SUMIF({ECD}, IF(ISDATE(@cell), MONTH(@cell)) = MONTH(TODAY() + 30), {$$})

Following Month: =SUMIF({ECD}, IF(ISDATE(@cell), MONTH(@cell)) = MONTH(TODAY() + 60), {$$}). 

 

Problem being that I have a control value in there to double check and the sums are coming out different. For instance for this month the formula I currently have =SUMIF({Mon}, "Oct", {$$}) is showing about 500k difference. 

Any ideas on what I am doing wrong?

Paul Newcome

The problem is that not every month has exactly 30 days in it. 

 

Instead of adding 30 to TODAY() then pulling the month. Try pulling the month of TODAY() and then adding 1. The only time you would run into problems with that is end of year when you will need to use an IFERROR.

 

Will you need to be able to account for more than 1 calendar year on a single sheet, or are you creating a new sheet for each year?

JVaihinger

I will need to be able to count for more then one year on a single sheet. 

Paul Newcome

Ok.

 

Current Month:

=SUMIF({ECD}, IF(ISDATE(@cell), AND(MONTH(@cell)) = MONTH(TODAY()), YEAR(@cell) = YEAR(TODAY())), {$$})

.

Now for the tricky part: Next Month...

 

First thought may be to just go with MONTH(TODAY()) + 1, but what if this is December? There is no Month number of 13. So we will use an IF statement to look at the month number and either look at next month in the same year, or the first month in next year.

To get the month we say "If the month of today is 12, then generate a 1, otherwise add 1 to today's month".

IF(MONTH(TODAY()) = 12, 1, MONTH(TODAY()) + 1)

 

And for the year we say if the month of today is 12, add 1 to the year, otherwise just generate the current year.

IF(MONTH(TODAY()) = 12, YEAR(TODAY()) + 1, YEAR(TODAY()))

 

Then we can take the formula for the current month...

=SUMIF({ECD}, IF(ISDATE(@cell), AND(MONTH(@cell)) = MONTH(TODAY()), YEAR(@cell) = YEAR(TODAY())), {$$})

 

drop the month formula into the month portion of the AND statement

=SUMIF({ECD}, IF(ISDATE(@cell), AND(MONTH(@cell) = IF(MONTH(TODAY()) = 12, 1, MONTH(TODAY()) + 1), YEAR(@cell) = YEAR(TODAY())), {$$})

 

then drop the year formula into the year portion of the AND.

=SUMIF({ECD}, IF(ISDATE(@cell), AND(IF(MONTH(TODAY()) = 12, 1, MONTH(TODAY()) + 1), YEAR(@cell) = IF(MONTH(TODAY()) = 12, YEAR(TODAY()) + 1, YEAR(TODAY()))), {$$})

.

Now on to 2 months out...

We will use the same idea as 1 month out, but with a slight twist.

The Month formula will actually be a nested IF statement. The first will be "if the month of today equals 11, generate a 1". The second IF will be "if the month of today equals 12, generate a 2", and the else portion will be to just add 2 to today's month number.

IF(MONTH(TODAY()) = 11, 1, IF(MONTH(TODAY()) = 12, 2, MONTH(TODAY()) + 2))

 

and the year is a little more straight forward. "If the month of today is greater than or equal to 11, add 1 to the current year, otherwise use the current year". Very similar for how we handled the year in the "Next Month" formula except accounting for months 11 and 12 instead of just 12.

IF(MONTH(TODAY()) >= 11, YEAR(TODAY()) + 1, YEAR(TODAY()))

 

then we just go through and drop these into the original formula the same as we did for "Next Month"'s formula.

=SUMIF({ECD}, IF(ISDATE(@cell), AND(MONTH(@cell)) = IF(MONTH(TODAY()) = 11, 1, IF(MONTH(TODAY()) = 12, 2, MONTH(TODAY()) + 2)), YEAR(@cell) = YEAR(TODAY())), {$$})

and the year...

=SUMIF({ECD}, IF(ISDATE(@cell), AND(MONTH(@cell)) = IF(MONTH(TODAY()) = 11, 1, IF(MONTH(TODAY()) = 12, 2, MONTH(TODAY()) + 2)), YEAR(@cell) = IF(MONTH(TODAY()) >= 11, YEAR(TODAY()) + 1, YEAR(TODAY()))), {$$})

.

.

.

This will give us our finalized formulas of...

 

Current Month: 

=SUMIF({ECD}, IF(ISDATE(@cell), AND(MONTH(@cell)) = MONTH(TODAY()), YEAR(@cell) = YEAR(TODAY())), {$$})

.

Next Month:

=SUMIF({ECD}, IF(ISDATE(@cell), AND(IF(MONTH(TODAY()) = 12, 1, MONTH(TODAY()) + 1), YEAR(@cell) = IF(MONTH(TODAY()) = 12, YEAR(TODAY()) + 1, YEAR(TODAY()))), {$$})

.

Two Months In The Future:

=SUMIF({ECD}, IF(ISDATE(@cell), AND(MONTH(@cell)) = IF(MONTH(TODAY()) = 11, 1, IF(MONTH(TODAY()) = 12, 2, MONTH(TODAY()) + 2)), YEAR(@cell) = IF(MONTH(TODAY()) >= 11, YEAR(TODAY()) + 1, YEAR(TODAY()))), {$$})

Kelly Drake

Thanks @Paul Newcome for this amazing layout. I had a similar issue with a COUNTIF .... but I had to swap out the IF(ISDATE(@cell) for an IFERROR() because I was getting an #INVALID DATA TYPE error.

Sharing what worked in case others come across this issue/need.

=COUNTIFS({Install Date}, AND(IFERROR(MONTH(@cell), 0) = IF(MONTH(TODAY()) = 12, 1, MONTH(TODAY()) + 1), IFERROR(YEAR(@cell), 0) = IF(MONTH(TODAY()) = 12, YEAR(TODAY()) + 1, YEAR(TODAY()))))

Paul Newcome

@Kelly Drake Glad you were able to get it working. I switched over to the IFERROR some time ago to the point where I forgot I had even used the ISDATE. Haha.