Excel subtotal if statements with autofilter

One of the many things I find Excel does well is counting and clustering data. So when I am trying to compare one data set against another, I am able, using the "CountIf" formula, to set up summary tables  at the top of worksheets, using formulae. Yes, while I know that pivot tables will do this for us, I tend to find these really clunky, and difficult to get working as I expect.

Ah, the joys of Excel. When it works, it works; and when it doesn't, we tear our hair out trying to work out what we need to redo so that what we have created returns the product we are seeking!

It is pretty easy to work out how to use autofilters, and to sum them (read here). However, trying to find out how to do a conditional countif formula sounds like it should be really easy; but I found it quite difficult to find. We could - for example, search for "Excel subtotal if statements" or "using If with autofilter in Excel", or "Subtotal with If statement", or "conditional Subtotal", or ...ad infinitum; but we will probably still find ourselves stuck for an applicable solution.

What we need to use is not CountIf, but CountIfs. I found out by accident how to construct CountIfs formulae: where, just like using multiple autofilters, we can permanently provide a range of conditionals for Excel to total. For example, if we enter 

=COUNTIFS($D$11:$D$611, "*"&$I3&"*",$E$11:$E$611,$K$2)

So let's look at how this works, element by element. First we have the function in red: "CountIfs". This allows us to count if certain conditions are met. Next we have the data ranges - which in this case are the columns where Excel looks for the data to count. Then there is the actual thing that Excel is going to look for, to count if Excel finds the comparison data.

1. Excel will look at cell K2 and count only data which shows that in column E, AND 
2. Further, Excel will only pick up that data which 'contains' the contents of cell 13. By contains, if cell I3 contained "when" it would count the contents of any cell which contained "when", "whenever" and "somewhen", but not "where". AND 
3. Excel will only count those items in the first column, column D, which meet both those conditions.

I hope you find this useful!

Sam