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 copypasting the data, obviously? I'm using Excel 2007.



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. 


Convert your data range to a table and add a totals row (Rightclick > Tables > Totals Row). By default this will display the Edit: (Reply to comment)



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 handpick 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. 


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 autofill 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 autofill down to populate those values IF(Z2=1,computenewderived value,error). 

