I have an Excel 2010 document that looks something like this:
Name Color Size Units
Bob Smith Red Large 5
Tim Jones Blue Small 8
Fred Wallace Red Small 1
Ann Smith Blue Large 8
Then, in the cells below my data, I have a formula that counts the number of people whose Units are less than 6:
This returns 2, as it should. The problem is that when I apply filters to the data, this value doesn't change. For example, if I filter for Column D = "Blue", I want the formula to return 0. If I filter for Column C = "Large", I want the formula to return 1.
I also have the same problem with this COUNTIFS function:
Is there a formula I can use that will perform the same function as COUNTIF / COUNTIFS, but will work with the filters?
Thanks for your help.