SUMIF with Operators


« Back to videos

Additional Information:

This tutorial shows you how to use operators e.g. greater than, less than symbols in conjunction with the SUMIF function. A basic SUMIF formula takes the form '=SUMIF(Range, Condition)'. The Condition may simply be to sum all positive numbers; in which case it would be written ">0".

If we could only use absolute values e.g. comparisons to 0, we would be very restricted in how we could analyze our data. Therefore, Excel also allows us to define our conditions relative to the different cells in the spreadsheet. This requires special syntax because cell references should never appear within quotation marks. This video demonstrates how ampersands can be used to link an operator, in quotation marks, to a cell reference outside quotation marks.

To sum all the data in column A that is greater than the entry in cell B1, you would use the formula '=SUMIF(A:A,">"&B1)'. Note that this is very different to the IF statement, where operators are used outside quotation marks e.g. '=IF(A1>B1, A1, 0)' would be the equivalent formula.