Pages

Wednesday 13 March 2019

Autosum COUNTIF Excel shortcut

An autofilter can be placed over your data by highlighting your active spreadsheet cells and clicking the autofilter icon.

 As I mentioned recently (here), we can do COUNTIF sums in Excel. However, a COUNTIF sum will include everything in the range, even if you are using an autofilter over your data. If there are 15 cells in total with the value you seek, but only 5 showing in the filtered range, COUNTIF will take no notice of the filter and keep returning 15.

 To sum only filtered results, once we have autofiltered our data, we then simply select a particular autofilter aspect that we are interested in - for example, specifying a "contains" from the dropdown list on each autofilter column header.

 Then, in our dashboard above our data (I always keep a few row free at the top of a spreadsheet for this) we can show the values - count, averages, sum - we seek using the subtotal function, and a number to indicate exactly what value it is that we want. "1" will average; "2" will give a count of numbers; "3" will return a count of text; "9" will sum (see a list of all these conditions here).

 You can get a lot more complicated with this, but in general, autofilters and the 'contains' function will get us a COUNTIF without headaches.
Sam

 To know more, go to:

Posted by
Labels: , , , ,

No comments :

Post a Comment

Thanks for your feedback. The elves will post it shortly.