COUNTIFS is not calculating correctly

After inserting this and playing around I've discovered it actually isn't working for me. When I use that formula it is adding any blanks in column L regardless of the month. Well at least the month prior. So it is double counting any blanks in the previous month.

I've reattached adding the "<=" & to both columns H and J and adding in a June expiration on the Renewal Chart Tab. You can see that it is double counting that blank cell once in column H and again in Column J. It should only show up in column H once I have this figured out.

Again it works perfectly in every other workbook but I must be missing something on this one that is causing it to return different results.

Thanks in advance for any help!

Originally Posted by heresteve2

When I use that formula it is adding any blanks in column L regardless of the month. Well at least the month prior. So it is double counting any blanks in the previous month.

That would be correct, the formula is only checking that the date in Renewal Chart!I:I is <=last day of the month of interest, not if the date specifically falls into that month.

If you want to limit the results to those contained within the month then you need to add another range and criteria to the formula

'Renewal Chart'!$I:$I,">" &EOMONTH(H$17,-1)

This will check that the date in column I is after the last day of the preceding month, eliminating the erroneous results you describe.

Similar applies to the column J formula, replacing H$17 with J$17.

If it works perfectly in your other workbooks then that must be down to coincidence that there are no records where the criteria overlap. In reality, they should all be producing the same incorrect results.

edit:-

Dates in column I of the 'Essex' file are all month end dates, that's why your original formula works there. The July dates in the 'Urban' file are date the 20th, not 31st, so the date doesn't match up with the month end.

When you spend too long looking at it, it becomes easy to miss things that you think should be obvious. If it's any consolation, I missed it the first time I went over it (and the second).

If you add the extra parameter to the formula, i.e.

=COUNTIFS('Renewal Chart'!$L:$L,"",'Renewal Chart'!$B:$B,$B25&"*",'Renewal Chart'!$I:$I,"<=" &EOMONTH(H$17,0),'Renewal Chart'!$I:$I,">" &EOMONTH(H$17,-1))

Then that will count everything that falls into the month specified in H17, regardless of the day of month shown in column I.

Or if you change the dates in column I so that they read as July 31 instead of July 20, then your original formula (Pre - John's edit) should work as it does with the other files.

Personally I would go with the edited formula in all files as a failsafe for future discrepancies in the raw data.