In today’s tutorial we’ll learn how to use the SUMIFS function in Excel to add all those values which are greater than a particular value. Let’s look at the dataset given below. It contains the sales data of various products from various locations. We’ll sum the sales of all those items whose sold quantity is more than a particular value from the dataset shown above.
Follow along the steps mentioned below to learn how to use SUMIFS with greater than condition in Excel. Let’s learn the syntax of SUMIFS and see what parameters are required to use this formula properly.
Syntax of SUMIFS
The syntax of the SUMIF formula is as follows,
=SUMIFS(sum_range, range1, criteria1…)
The difference between simple SUMIF and SUMIFS is that the latter can have multiple conditions in one formula. However, we’ll use only one condition to keep things simple.
This is the range (E5:E15 in our case) from which we’ll pick the values to add them whenever the condition inside the criteria range is met the corresponding value from the sum_range will be picked and added to the sum.
This is the first range which will be checked against the criteria. We’ll call it the criteria range. In our case this will be D5:D15.
Criteria1: This is the condition or criteria that will be checked inside the range1. The important thing to note here that the criteria or condition will be written in text format i.e., inside double quotes. Even if it is a condition involving only numeric value comparison. Therefore, we can use “>175” or “>”&G5 to make the criteria value dynamically modifiable. In the latter case, G5 will hold the value which will be compared or checked inside the given range. The ampersand sign & concatenates the greater than symbol and the value from G5 and converts them in text format. Therefore, we can use this combination as the text condition inside the SUMIFS function.
Excel is a very handy software tool for organizing and manipulating any type of datasets. It has many functions for calculations and data visualization. It also provides us with a lot of functions for logical decisions and then we can use the results of these functions to perform some mathematical calculations.
Step 1 – Choose a cell for dynamic criteria
– Select a cell outside the data table.
– Give it a suitable name. For our example, we’ll use “Quantity to Check”.
– Choose an appropriate value which will be checked inside the criteria range. We’ll choose 175 as the checking criteria, as shown above.
Step 2 – Implement the formula with SUMIF
– We’ll use the following formula for checking and adding the sales values for all those items for which the sale quantities are more than 175, as shown above.
Step 3 – Change the criteria dynamically
– We have set up the SUMIF function in such a way that when we change the value in cell G5, the SUMIF greater than criteria will be updated and new results will be calculated automatically as shown above.