Does Excel have a row filtering function (more general version of SUMIF/COUNTIF)

Question

SUMIF and COUNTIF functions are handy for simple SUM and COUNT operations on only some of the values in a range, but is there a more general version that would allow me to plug in any other function I want? For example, I may want to get the standard deviation of values matching some criteria - but there is no STDDEVIF. If not, what's the best way to do this, without copy-pasting the data, obviously? I'm using Excel 2007.

Answer 1

You should look into Database functions. They are much more powerful than Sumif in that the criteria is unlimited. It can be as complex or simple as you need. They also have standard deviation functions within the database category. They don't allow you to insert ANY function you want, but it would take care of your std dev problem.

Answer 2

Convert your data range to a table and add a totals row (Right-click -> Tables -> Totals Row). By default this will display the SUM but you can easily change it to a range of other functions, including STDDEV. Then apply a number filter to the table column.

Edit: (Reply to comment)

• Make sure your columns of data all have a name in the first row.
• Select the range, including the first row containing the names.
• Press Ctrl + L (Windows) and make sure you have selected "My table has headers". Click OK.
• You should now see that Excel has applied new formatting to your data and you have drop-down filters on each column header - you now have an Excel table.
• You can add a total row by right-clicking anywhere on the column you want a total for. Now select Tables -> Totals from the pop-up menu. By default you will get the total (SUM) of the data in the column.
• Select the cell containing the total and a drop down arrow will appear to the right of it. You can click this to change the function from SUM to your desired function, such as StdDev.
• The value is calculated from whatever values are visible in the column. You can apply filters to one or more of the columns by clicking the drop-downs that appear in the header rows and using number or label filters. The calculated value will change accordingly.

Answer 3

Not really an answer, but something to consider: Excel doesn't have more complex xxxxIF() functions because there's not a community likely to use them. Your examples, SUMIF() and STDEVIF() would be used by very different communities. It's very easy to see an accountant wanting to pull all the transactions for a specific customer out of a global register (SUMIF). Statisticians, on the other hand, have to very carefully justify removing any value from a data set. So automatic filters are unlikely; the data would be adjusted by hand before analysis (STDEVIF). Consequently, this is going to take some degree of hand manipulation, or very complex cell formulas.

Is there some reason you don't want to add a temp table for scratch space? I'd quite happily insert a worksheet, and either hand-pick the data to copy, or write formulas to pull data out of the original worksheet. Once you've got the "IF" part taken care of, analyze away. You can delete or hide the scratch worksheet, when you're done.