COUNTIFS is not calculating correctly

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.