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.

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

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

- Prepare your amount-range table

*Figure 2 – Showing the amount-range table*

- 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*

- Slot in your ‘SUMIF greater than’ formula

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

- 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,

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

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

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

*Figure 7 – Showing amount range table with $4000 label*

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

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

- 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*

- 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.

## Leave a Comment