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

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.

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.
Excel Book Excerpts
Previous | Next

MrExcel.com & related websites debuted on November 21, 1998.

ExcelArticles.com provides examples of Formulas, Functions and Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Formulas, Functions and Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.

This site contains affiliate links. Any affiliate commissions that we earn when you click a link to Amazon or other sites is reinvested in keeping MrExcel.com and ExcelArticles.com running.
You can earn a commission for sales leads that you send to us by joining our affiliate program.

Privacy Policy | Cookies Policy

Excel® is registered trademark of the Microsoft Corporation.
MrExcel® is a registered trademark of Tickling Keys, Inc.

© 1998 - 2024 by MrExcel Publishing. All rights reserved.