Subtotal occurrences by month with filters

[JalopyJeep]

I am currently trying to get a total number of entries made during each month within that year. I have been able to use the countifs function successfully i just need it to work with filters.

This is my current formula that i would like to work with filters.

=COUNTIFS(L19:L9994,">="&"5/1/2015",L19:L9994,"<="&"5/31/2015")

Below is an example of the spreadsheet i would like to have populate as the sheet is filtered.
[TABLE="width: 231"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]2016[/TD]
[TD]TOTAL[/TD]
[/TR]
[TR]
[TD]January[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]126[/TD]
[TD="align: right"]126[/TD]
[/TR]
[TR]
[TD]February[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]105[/TD]
[TD="align: right"]120[/TD]
[/TR]
[TR]
[TD]March[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]107[/TD]
[/TR]
[TR]
[TD]April[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]66[/TD]
[TD="align: right"]95[/TD]
[/TR]
[TR]
[TD]May[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]64[/TD]
[/TR]
[TR]
[TD]June[/TD]
[TD="align: right"]140[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]177[/TD]
[/TR]
[TR]
[TD]July[/TD]
[TD="align: right"]164[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]175[/TD]
[/TR]
[TR]
[TD]August[/TD]
[TD="align: right"]86[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]86[/TD]
[/TR]
[TR]
[TD]September[/TD]
[TD="align: right"]76[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]76[/TD]
[/TR]
[TR]
[TD]Octover[/TD]
[TD="align: right"]133[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]133[/TD]
[/TR]
[TR]
[TD]November[/TD]
[TD="align: right"]152[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]152[/TD]
[/TR]
[TR]
[TD]December[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]110[/TD]
[/TR]
[TR]
[TD]TOTAL[/TD]
[TD="align: right"]962[/TD]
[TD="align: right"]459[/TD]
[TD="align: right"]1421[/TD]
[/TR]
</tbody>[/TABLE]

I have already used pivot tables but would like a different method for totaling.

Thank you in advance for any help and tips,

Jeremy

 

[Jonmo1]

I would recommend adding a helper column, say column M for example
In M19 and filled down to M9994 put
=SUBTOTAL(103,L19)

Then use
=COUNTIFS(L19:L9994,">="&"5/1/2015",L19:L9994,"<="&"5/31/2015",M19:M9994,1)

 

[JalopyJeep]

I was not able to get this to work, i kept being returned a value of 0. also i cannot add to or modify the spreadsheet.

Thank you for the help Jonmo1.

 

[Jonmo1]

Don't see why that wouldn't work.
Did you put it in exactly as I posted? It was tailored specifically for the formula you posted. Don't change what I posted.



On a personal opinion note:

I'm always discouraged when I hear this

also i cannot add to or modify the spreadsheet.

Why Not?
Doesn't creating this very formula itself count as a modification to begin with?

It's not that you physically can't, excel gives you 16384 columns to work with, surely you haven't actually used them all already.
So it must be the old 'upper management says so'

Well, they've delegated the task to you, so assert yourself and say something like
"In my expert opinion (which you've tasked me to use), this is the best way to accomplish the task"

The extra used column can be hidden, and it doesn't even need to be on the same sheet.

 

[JalopyJeep]

Got it to work.

And yeah its the old upper management excuse unfortunately. I dont make the rules i just play by them.

Thank you again for the help, really appreciate it.

 

[Jonmo1]

You're welcome.

Mind you, it CAN be done without the helper column. I know there are other formula gurus here that know how to do it.
But it's out of my wheelhouse, simply because I choose to not do it.
The helper column is far more efficient than the methods I've seen to incorporate it directly into one formula.

So stick around, someone else may come by and do it in one formula.

 

[Aladin Akyurek]

If needed...

=SUMPRODUCT(
    SUBTOTAL(2,OFFSET(L19,ROW(L19:L9994)-ROW(L19),0,1)),
    --(L9:L9994>="5/1/2015"+0),
    --(L9:L9994<="5/31/2015"+0))

 

[JalopyJeep]

Thats awesome! Thanks Aladin.

 

[Aladin Akyurek]

Thats awesome! Thanks Aladin.

You are welcome.