< Go Back

Sum if greater than

Excel provides several very useful functions that increase accuracy as well as save the time spent doing calculations manually. In order to calculate the sum of a set of values on the premise that the value meets a particular condition, the “Sum if greater than”. Sum if greater than works using the SUMIF formula.

The SUMIF formula is great for tracking values and can function with several logical operators.

Formula

=SUMIF(range,">criteria")

How to use Sum if greater than

Sum if greater than is simple to use, as long as you can identify what the criterion is:

  •      criteria: This represents the value that serves as the condition for the formula. It is to be written within the parenthesis, and it houses the logical operator to be used also.
  •      Range: This represents that set of values that will serve as the basis for comparison e.g. A2:A8. “A” in the illustration is for the column.

In Excel, there are several logical operators that are available for use. Some of them include <, >, >=, <=, = etc.

The SUMIF function adds all the values which correspond with the criterion stated. For instance, if SUMIF function is to work on a list of values, the logical operator included as the criterion, would determine what should be evaluated.

If you want the criterion to be dynamic, i.e., you want it to be easily altered, then use:

=SUMIF(range,">"&A1)

The cell location A1 can always change, and hence, the result will always be different pending the alteration made to the cell. This method is called ‘using a cell reference’.

Example 1

Let us assume that you have an expense tracking sheet and you need to find the sum of all your expenses that went beyond $10,000  in the month of July.

The following steps should be taken.

  1. Paste the expense sheet showing the amount spent on items, and the dates.   

Figure 1 – Showing the expense sheet with dates, items and amounts

  1. Prepare your amount-range table

Figure 2 – Showing the amount-range table

  1. Declare the range; in this case, the 1st to the 8th of July

Figure 3 – Showing the range of dates to be tested using amounts

  1. Slot in your ‘SUMIF greater than’ formula

Figure 4 – Showing the format with which the formula should be written

  1. View Result

Figure 5 – Showing the result of the sum if greater than 10000

Example 2

Using the same expense sheet illustration above,

If you want to use cell by reference i.e.

using =SUMIF(range,">"&A1) instead of =SUMIF(range,">criteria")

Following the example above,

  1. Paste the expense sheet showing the amount spent on items, and the dates.

Figure 6 – Showing the expense sheet with dates, items, and amounts

  1. Prepare your amount-range table: using $ 4000 as a criterion

Figure 7 – Showing amount range table with $4000 label

  1. Declare the range; in this case, the 1st to the 8th of July

Figure 8 – Showing range of date to be tested using amount

  1. Specify what cell amount you want to use in the cell by reference formula

Figure 9 – Showing the cell by reference formula – highlighting cell c6

  1. View Result

Figure 10 – Showing the result of the sum if greater than 4000 naira – using a cell reference

Notes

  • When using the formula in example one, always include the logical operator in the same double quotes as the static criterion.
  • If for any reason, you need to handle multiple criteria, use SUMIFS function.
Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar