I am trying to use the sumif function, but I have 2 conditions I need it to meet. Is there a way to nest two sum if functions so that it will sum only when the 2 criteria's are met?
OK, if you'd like to do a conditional sum with multiple conditions, you have several alternatives. One way is by using the Conditional Sum Wizard. It is an add-in that needs to be installed. In Excel 2003, go to Tools > Add-Ins, and then select Conditional Sum. In Excel 2007, Excel Options > Add-Ins > Manage Excel Add-ins > Go. Then, you'll have access to the Conditional Sum Wizard. In Excel 2003: Tools > Conditional Sum. In Excel 2007: Formulas > Conditional Sum. Opening the wizard enables you to specifiy several conditions, plus lots of other cool options.
Another approach is with the Excel 2007 SUMIFS() function. This is similar to the SUMIF() function, except that it allows for multiple sets of conditions.
Condition is a comparison, like $A$2:$A$10="jeff" or $A$2:$A$10>5
SumRange is the range of cells with values to sum if the Conditions are met, like $C$2:$C$10
Note that the - in the sumproduct formula above does not mean "minus", it tells Excel to evaluate the Conditions as numbers intead of boolean and enables this function to work properly in this context
Also, if you have an even number of Conditions, then you should be good. If you have an odd number of conditions, then you may need to flip the sign of the result.
Conditional Sums
Hi man!
OK, if you'd like to do a conditional sum with multiple conditions, you have several alternatives. One way is by using the Conditional Sum Wizard. It is an add-in that needs to be installed. In Excel 2003, go to Tools > Add-Ins, and then select Conditional Sum. In Excel 2007, Excel Options > Add-Ins > Manage Excel Add-ins > Go. Then, you'll have access to the Conditional Sum Wizard. In Excel 2003: Tools > Conditional Sum. In Excel 2007: Formulas > Conditional Sum. Opening the wizard enables you to specifiy several conditions, plus lots of other cool options.
Another approach is with the Excel 2007 SUMIFS() function. This is similar to the SUMIF() function, except that it allows for multiple sets of conditions.
Good luck with your project!
Thanks
Jeff
Works like a charm!!!
WOW, the SUMIFS function was exactly what I was looking for. This is a very cool new function to Excel 2007.
Works like a charm!!!
Thanks Jeff
Additional Approach
In addition, if you are in Excel 2003 and don't have access to SUMIFS() then you could perhaps use =SUMPRODUCT() to accomplish it as follows:
=SUMPRODUCT(-(Condition),-(Condition),SumRange) where:
Note that the - in the sumproduct formula above does not mean "minus", it tells Excel to evaluate the Conditions as numbers intead of boolean and enables this function to work properly in this context
Also, if you have an even number of Conditions, then you should be good. If you have an odd number of conditions, then you may need to flip the sign of the result.