COUNTIFS filtered by month

**HeathWilD** · 09-13-2012, 08:12 PM

Hi, great site very helpful community so far, hopefully I can help others one day.
Today however I need some input on how to get this COUNTIFS to return a single number
=COUNTIFS(ZI_ENH_TAB!B:B,"PM01",ZI_ENH_TAB!CS:CS,MONTH(1)) what I am trying to achieve is to count the number of "PM01" values returned in column B but only for a given calendar month from the list of dates in column CS. the count of specific values in column B I can get right, it is counting that value only by the specified month I am having trouble with??

Please see attached sample sheet.

Kind regards

Heath

**HeathWilD** · 09-13-2012, 08:18 PM

Sample attached

**FDibbins** · 09-13-2012, 08:46 PM

you are trying to find text (Jan) in a value/date (1/9/12)

i have made some minor modifications to your file, see if this is something you can work with. you can hide the styff i added if you want

**HeathWilD** · 09-13-2012, 11:50 PM

I can work with that, I was hoping to avoid any manual intervention as the intent is to import the data monthly directly from SAP (manually as it will cost us $45K for any custom reports to be generated) Adding in one column will only add a minute or two into the process so I will say thanks!

I would still love to know how to get the formula to do the date filter automatically for me however? Need to engage my lateral thinking when it comes to these equations...I won't marked as solved just yet as if there is a better way I would love to see it.

**FDibbins** · 09-15-2012, 05:06 PM

the only "manual" intervention would be for the user to copy the formula in sheet2 col DT down

also, you could change the forula on sheet1 G12-G23 from

=COUNTIFS(ZI_ENH_TAB!DT:DT,$E12,ZI_ENH_TAB!B:B,G$11) to
=COUNTIFS(ZI_ENH_TAB!DT:DT,MONTH($F12),ZI_ENH_TAB!B:B,G$11)
and do the same for the formulas in col H