1

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.

4 Answers 4

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.

1
  • I ended up implementing STDEVP myself, but the function DSTDEVP does look like it would have done what I want, at least for this case. Since it looks like there is no good way to plug in any function I'm accepting this.
    – EMP
    Jul 23, 2010 at 0:02
1

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.
4
  • Sorry, I couldn't find how to do this. By "table" do you mean a Pivot Table? I've never used one before and while I can create one easily I can't figure out how to make it do what I want. Essentially, I have two columns, A and B. Column A has only a small number of distinct values (say 10). For each of those I want to know the standard deviation (or other formula) of all the corresponding values in column B.
    – EMP
    Jul 15, 2010 at 1:37
  • @Evgeny: Please see my edit above. Jul 15, 2010 at 2:52
  • Thanks, I got that to work. However, I'd like to get the value of this function for each of the column A values, not one at a time, because I want to create chart with them (along with other values). For STDEVP I've had to basically implement my own using SUMIF and COUNTIF, but for more complex functions it looks like this may not be possible.
    – EMP
    Jul 15, 2010 at 3:35
  • @Evgeny: In that case, I would go with (a) @variant's suggestion of custom VB functions or (b) create additional columns that hold filtered values from column A and use the table method I suggested. Jul 15, 2010 at 4:21
0

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.

0

Another approach that you want to consider is usage of a "Helper" column to flag filtered data:

You may add a column of data and use that column to determine whether a row is hidden (filtered) or not. With values in Col A nominally populated with data, you could use say Col Z (new Filter column) with the formula "=subtotal(3,A2)" (starting in row 2) and have Excel auto-fill down to the end with the Cntrl+ handle. Once setup, you may optionally hide your new Filter column of data.

Once you have your new Filter col Z setup, you may then use it with either COUNTIFS or just normal IF conditionals. With COUNTIFS you could use it similar to COUNTIFS(Data_Rng,Data_Val,Filter_Rng,1) which will filter out the counting of Data_Rng for values equal to Data_Val only in rows not hidden determined by Filter_Rng equal to 1. Be sure to keep your ranges matched in length, otherwise Excel will generate an error.

If COUNTIFS does not give you what you need, you may also setup derived conditional values based upon the Filter Z column and use the Excel auto-fill down to populate those values IF(Z2=1,compute-new-derived value,error).

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .