As you know, Microsoft Excel is an advanced application that provides users with a wide range of functions and features for data analysis, financial modeling, and business intelligence. It includes several calculation functions, such as COUNT and SUM. The COUNT function counts the number of cells, whereas the SUM function counts numbers.
You have come to the right place if you are a beginner and don’t know how to conduct calculations using SUM, SUMIF and SUMIFS in Excel.
Here’s a quick overview of the differences between SUMIF and SUMIFS, what sum is, sum vs sumif, when to use SUMIF and SUMIFS, and examples so you can read and do calculations with ease.
What is SUM in Excel?
As the name implies, SUM is the most basic and straightforward tool that we use to add numbers in Excel. It is used to simply add up numbers in a group of cells. The SUM function is useful when you need to add up numbers without any conditions. It doesn’t care about conditions or filters; it simply totals up everything you tell it to
It is perfect for quick computations such as totaling revenue, adding expenses, and summing test results.
Syntax:
=SUM(number1, [number2], ...) Or =SUM(A1:A5)Example:
Let’s suppose that you want to total sales figures, calculate the sum of exam scores, or add up any list of numbers. Use this formula:
=SUM(A1:A10)This will add all numbers in the range A1 to A10.
What is SUMIF in Excel?
The SUMIF function allows you to sum values based on certain criteria and conditions. It allows you to apply a single rule to determine which numbers should be added. If you are working and want to add values or data selectively, SUIF may be the best choice for you.
Syntax:
=SUMIF(range_to_check, condition, [range_to_sum])- range: This is the range of cells in which you want to perform calculations.
- Criteria: This will be the condition that you will apply to sum specific values.
- sum_range: (Optional) The actual cells to sum. If you forget to mention the sum range, Excel will sum the cells in the check range.
Example:
Let’s say that you are selling fruits and you want to sum up the sales of Apples, you can use this formula:
=SUMIF(A2:A10, "Apples", B2:B10)This will look through A2:A10, find cells that say “Apples,” and then sum the corresponding values in B2:B10.
SUMIFS in Excel
Here comes the most advanced sum function, SUMIFS. This function lets you sum values based on multiple conditions and criteria. SUMIFS was released in 2007, but it is currently accessible in all Excel versions, including 2010 and Excel 365.
Syntax:
=SUMIFS(range_to_sum, condition_range1, condition1, condition_range2, condition2, ...)- range_to_sum: These are the numbers you want to add up.
- condition_range1: The first column (or group of cells) where Excel will look to check your first condition.
- condition1: The first rule that tells Excel what to find in condition_range1.
- condition_range2: Another column to check.
- condition2: A second rule that should also be true.
- You can keep adding more pairs like this for more conditions.
Example:
Suppose you want to sum sales of a specific product in a specific region during a specific month. You can use this formula:
=SUMIFS(C2:C10, A2:A10, "Apples", B2:B10, "North")This Formula will add the values in C2 to C10 only if the matching cell in A2 to A10 says “Apples” and the matching cell in B2 to B10 says “North”.
SUM Vs SUMIF Vs SUMIFS
Here are some key differences between SUM, SUMIF, and SUMIFS in Excel to help you understand them better.
| Feature | SUM | SUMIF | SUMIFS |
| Conditions Supported | None | One | Multiple |
| Formula Complexity | Very simple | Simple | Slightly complex |
| Usage Simplicity | Very easy | Easy | Moderate |
| Data Filtering | No | Yes | Yes |
| Best Use Case | Total of all numbers | Total with one condition | Total with many conditions |
SUM is ideal when you want to add all values without any filters.
SUMIF is great for applying one condition, like summing all sales for one product.
SUMIFS is useful when you need to add numbers based on more than one condition, like product and region together.
When To Use SUMIF and SUMIFS in Excel?
You should use the SUMIF function in Excel when you need to add values based on a single condition, such as summing numbers related to one specific item or category. It’s simple and efficient when only one criterion is involved.
On the other hand, SUMIFS is the better choice when your calculation depends on multiple conditions being met at the same time. It allows you to apply two or more filters across different columns, making it more suitable for complex scenarios where you need to control the criteria more precisely.
SUMIF and SUMIFS in Excel Examples
1. Using SUMIFS with Comparison Operators and With Dates
You work in a small shop that sells fruit. You maintain a sales record in Excel and want to find the total Apple sales where the amount is greater than 100. You can use this formula:
=SUMIFS(D2:D9, B2:B9, "Apple", D2:D9, ">100")Enter the formula in an empty cell.
Now you can see in the image that the formula has Sum amounts greater than 100, and the result is 270.
2. With Dates
Now you want to find the total Apple sales between 10th March 2025 and 31st March 2025. Enter this in any empty cell or results cell.
=SUMIFS(D2:D9, B2:B9, "Apple", A2:A9, ">=10-03-2025", A2:A9, "<=31-03-2025")
Now you can see in the image that the formula has returned 300, the total of Apples from 10 to 31st March.
SUMIF and SUMIFS in Excel: Using With OR Criteria
The SUMIF function does not support OR logic; instead, it only works with AND logic. See these examples if you want to sum values based on multiple criteria.
1.SUMIF + SUMIF Example:
As mentioned before, the SUMIF function does not operate directly with OR logic; instead, you can combine multiple SUMIFs, one for each condition.
Let’s say you want to find the total sales of Apple OR Mango.
Use this formula:
=SUMIF(B2:B9, "Apple", D2:D9) + SUMIF(B2:B9, "Mango", D2:D9)The first SUMIF function looks for the total sales of “Apple,” and the second SUMIF function looks for the total sales of “Mango.” Then, all the sales are summed, and you get the total sales.
SUMIFS + SUMIFS
If you want to sum multiple products using various conditions rather than just an OR function, you can use the SUMIFS function.
Let’s say that now you want to find the total sales of Apple in March + Mango in March.
Formula:
=SUMIFS(D2:D9, B2:B9, "Apple", A2:A9, ">=01/03/2025", A2:A9, "<=31/03/2025") + SUMIFS(D2:D9, B2:B9, "Mango", A2:A9, ">=01/03/2025", A2:A9, "<=31/03/2025")
You can see that the formula returns the total sales of “Apple and Mango” in March.
2. Using SUM and SUMIF With An Array Argument
Using SUMIF +SUMIF appears to be quite straightforward; simply add more criteria by adding a plus sign. What if you want results based on multiple criteria and OR conditions? The previous formulas, which appear to be too short and simple, will become large and complicated when adding up values on an extensive list of criteria. So, to resolve this issue, an array argument is introduced.
In the array argument, simply place all of your conditions in curly braces separated by commas.
In addition, in the array argument, we will need to use the SUM function in the formula. If we simply use the array argument, the SUMIF function will only check the first criterion in the array argument and provide the results based on it, because the conditions are written in a single cell. So, to use an array argument correctly, you must wrap your formula in a SUM function.
=SUM(SUMIF(B2:B9, {"Apple", "Mango"}, D2:D9))
As you can see in the image, by using a minimalist formula, you can also get similar results. You can add more and more criteria to the array arguments without making the formula as difficult as using SUMIF+SUMIF. Additionally, you can use the array argument with both text and numbers.
Using SUM and SUMIFS With An Array Argument
You can also use an array argument with SUMIFS to make formulas simpler and less difficult. Using the previous example, you can apply this formula and get the same results.
=SUM(SUMIFS(D2:D9, B2:B9, {"Apple", "Mango"}, A2:A9, ">=01/03/2025", A2:A9, "<=31/03/2025"))By using this formula, you will get the same results.
3. Using SUMPRODUCT WITH SUMIF
If you want to define cell ranges rather than criteria directly in the formula, you can do so using the SUMPRODUCT function just like that.
=SUMPRODUCT(SUMIF(B2:B9, G2:G3, D2:D9))
Using SUMPRODUCT With SUMIFS
You can also use the SUMPRODUCT function with SUMIFS to achieve similar results as with SUMIF.
=SUMPRODUCT((B2:B9="Apple")*(A2:A9>=DATE(2025,3,10))*(A2:A9<=DATE(2025,3,31))*(D2:D9))SUMIF And SUMIFS In Excel: Conclusion
Understanding when and how to use SUMIF and SUMIFS in Excel can make a huge difference in the way you analyze your data. SUMIF is a great option when you’re working with a single condition and need a quick, focused result. On the other hand, SUMIFS gives you the flexibility to apply multiple criteria across different ranges, making it ideal for more advanced and detailed filtering. Whether you’re tracking sales, organizing expenses, or working with date ranges and categories, knowing the difference between SUMIF and SUMIFS in Excel helps you work smarter and build formulas that match your specific needs.
Check out “What is AutoSum in Excel, How Do You Use it?“
