• Excel Book Excerpt

Excel Can the Results of a Formula Be Used in SUMIF?

This page created on Friday, July 1, 2016 by Bill Jelen

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Can the Results of a Formula Be Used in SUMIF?

Problem: Can the results of a formula be used as the criteria? I would like to add all numbers that are above average.

Strategy: The second parameter of the SUMIF/COUNTIF can be a calculation, but you must concatenate a comparison operator in quotes with the formula. Consider this formula:

=SUMIF($F$6:$F$61,">"&AVERAGE($F$6:$F$61),$F$6:$F$61)

The criteria is “>"&AVERAGE(F6:F61). Excel first calculates the average, then joins the operator with the result. In the second step of evaluating the formula, Excel has changed the formula to “>39535.71".

  1. The criterion is built from a formula.

<-Previous Topic Next Topic->

For more resources for Microsoft Excel:

privacy policy