Tag: Excel Tips and Tricks

In excel adding a range with one condition can be done with conditional formula called sumif. The conditional sum worksheet function  sumif adds the specified range based on the criteria. The sumif conditional sum accepts only one criteria. If you want to perform sum based on multiple criteria, then you need to use multi conditional sum formula sumifs.

Syntax of conditional sum sumif Worksheet Function

Syntax: =SUMIF(range, criteria, [sum_range]).

Range: The range of cells that you want evaluated by criteria. Cells in each range must be numbers or names, arrays, or references that contain numbers. Blank and text values are ignored.
Criteria: The criteria in the form of a number, expression, a cell reference, text, or a function that defines which cells will be added. For example, criteria can be expressed as 32, “>32”, B5, 32, “32”, “apples”, or TODAY().
Important Note:  Any text criteria or any criteria that includes logical or mathematical symbols must be enclosed in double quotation marks (“). If the criteria is numeric, double quotation marks are not required.
sum_range:The actual cells to add, if you want to add cells other than those specified in the range argument. If the sum_range argument is omitted, Excel adds the cells that are specified in the range argument (the same cells to which the criteria is applied).
Note: If you dont define sumrange then excel adds the range based on criteria. sumrange is needed if you want add other than criteria range.

Examples for sumif worksheet function

Below are the example for conditional sum in excel.

So sumif excel formula to add only Apple is
=SUMIF(A2:A6,”Apple”,B2:B6)
If you are having multiple criteria for sum then you should use  multi conditional sum refer to my previous post on multiconditional sum.

You can also use Wild card charectors with SUMIF function. In that way you can even extend the usage of SUMIF functions.

In our daily work in excel, there are situations,  which requires summing of range based on multiple conditions. In this scenario, multi conditional sum  formula of excel comes useful. Starting Excel 2007, we have a direct excel formula for multi conditional sum. The excel versions prior to 2007, we can use sumproduct excel function.
SUMIFS is the multi conditional formula in Excel. For single criteria sum, we can use SUMIF function. Syntax of SUMIFS and SUMIF are slightly different. Learning these two functions, will help to work efficiently with Excel.

Recommended Reading:

How to use SUMIF function in Excel

Multi Condition sum in Excel 2007, Excel 2010 and Later

Syntax of multi condition formula Sumifs is
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
Examples for multi condition sum:
sum the numbers in the range A1:A20 only if the corresponding numbers in B1:B20 are greater than zero (0) and the corresponding numbers in C1:C20 are less than 10.
Formula is: =SUMIFS(A1:A20, B1:B20, “>0”, C1:C20, “<10”)

Multi Condition sum in Excel 2003 or older

The alternate function for SUMIFS is sumproduct function in Excel. It does summing of ranges, based multiple sets of conditions. It is not as simple as SUMIFS. But once practiced and learned the logic, very useful.
Syntax of multi condition formula sumproduct:
=sumproduct((Criteria1)*(Criteria2)………..(CriterialN), Sumrange)
Note: For using sumproduct as multi conditional sum, the ranges needs to be even.
Let us take above example with sumproduct:
=sumproduct((B1:B20>0)*(C1:C20<10)*(A1:A20))

Multi condition sum using array function: Using array function for multi condition sum is not recommended as that takes more time and the formula is tedious and it requires every time to Shift+Ctrl+Enter. So i omitted array function here.

This Excel tips and tricks I tried to cover the multi conditional sum in Excel. For your reference, below are the few other useful functions of excel.

1. COUNTIF — COUNTIF excel function is useful for single criterion count.
2. COUNTIFS- This excel function is useful for multi conditional count.

There are many other count formulas in Excel. These are very commonly used functions of Excel.