Excel SUMIF Formula (Table of Content)
SUMIF Formula in Excel
In this article, we will learn about the SUMIF formula in Excel. We will also look into the use of the function with several examples.
The sum function adds cells or all cells in a range. The SUMIF function is used to add cells or a range when a specific criteria is met. Thus, this function adds all the cells that fulfill a certain condition or criteria. A criterion could be a number, text or a logical operator. In short, it is a conditional sum of cells in a range.
This function is a worksheet function and is available in all versions of Microsoft Excel. It is categorized under Math & Trigonometry formulas.
The formula is written as below:
How to Use SUMIF Formula in Excel?
The syntax has arguments as mentioned below:
- Range – It is a required argument. It is the cell range on which the condition/criteria is applied. The cell should contain number or text, dates, cell references that have numbers. If the cell is blank, it will be ignored.
- Criteria – It is a required argument. It is the criteria that will decide which cells to be added. In other words, it is the condition on the basis of which the cells will be summed up. The criteria can be text, number, dates in excel format, cell references or logical operators. It could be like 6, “yellow”, C12,”<5”, 05/12/2016, etc.
- Sum_ range – It is the cell range which needs to be added. It is an optional argument. They are the cells other than those found in range. In case there is no sum range the original cell range is then added.
How to Use SUMIF Formula in Excel?
SUMIF formula in excel is very simple and easy to use. Let’s start with a very simple example of SUMIF. In this example, we only have one column or range.
Example #1
This is a very basic example, with only two arguments.
Below is the single column data on which we will use SUMIF. We need to add cells that are above or >20, in the cell range A1: A8.
We will now write the formula in cell A9 as below:
In this the range is “A1: A8”, criteria is “>20” and then since there is no sum_range so the cells of range (A1: A8) will be added.
Press Enter, to get the result.
All cells above 20 are added in the range. Only cell A3 that has 15 and cell A5 that has 10 are excluded.
Also, we can use a cell reference as criteria, instead of hard-coding the actual value.
Here we have used E4 and E5 respectively as cell references instead of 20 and 40. Now press Enter to see the result.
This is how we use cell references as criteria’s instead of writing them.
Example #2
In this example, we have a data set of different fruits and their stocks as shown below. Suppose we want to know the total stock of mangos out of all fruits.
We will now write the formula in cell E3:
The arguments in the formula are explained below:
Range: A2: A11. In this range, the criteria of mangos will be applied.
Criteria: It is mangos, as we want to know the sum of mangos out of all fruits.
Sum_range: The sum range here is B2: B11.
Press Enter to get the result.
We have 450 mangos out of all fruits.
Example #3
We have sales data from different zones. Let’s say we want to know the total sales for South zone.
We will write the formula in cell B12 as below:
Range: A2: A10
Criteria: SOUTH
Sum_range: B2:B10
Press Enter.
So the total sales for the south zone are 197. Also, we can use a cell reference in criteria instead of south.
Here A3 is a cell reference used in place of “South”.Now press Enter.
So we can also use cell references in place of writing the criteria.
Example #4
We have sales data table for six months. And we want to find out the total sales for the month of May.
We will write the formula in cell B16.
Once you press Enter you will see the Result.
We will now be looking at some different SUMIF scenarios where the criteria’s are text.
Example #5
We have a sample data of balloons and their quantities.
Let’s say we want to sum up all blue balloons, be it sky blue or navy blue, out of all balloons.
We will write the formula as below:
Press Enter to get the result.
In this case, we want to sum cell based on a partial match. So we use a wild card *. Hence all cells that contain the word “Blue” will be summed up, be it single or in combination with other words.
And so Blue, sky blue and navy blue are all summed up.
In case you want to add all balloons, except for blue balloons. We will write the formula as below.
The criteria in this example are “<>Blue”, which means add all except for blue balloons. It will also add sky blue and navy blue in this case.
Press Enter to see the total balloons except for blue balloons.
Now, if we want to add all balloons except for all shades of green color. For this, we will write this formula.
The criteria in this formula are any cell containing word green or in combination with other words like dark or light green will not be summed up.
Hence we see all balloons are summed up except for green, dark green and light green balloons.
Also, we can use cell references in criteria when using logical operators.
Example #6
Taking our previous example if we want to add all balloons except for red balloons.
Press Enter to see the Result.
In case you want only a specific cell or “= to” operator you can use any of the options shown below. Suppose, we only need the sum of Pink balloons. We will write the formula by any of the ways.
Or
Both of them will give the below answer.
Let’s now see how to add blank cells using SUMIFs.
Let us say we have the above data table with blank cells and we want to sum up the blank cells. We can do so by writing the below formula.
To see the sum of blank cells, press Enter.
Here the logical operator “=” is used for blank cells.
The above examples would have given you a clear understanding of the function and its uses.
Things to Remember in sumif formula in excel
- To make things simple, make sure that range and sum_range are of the same size.
- If the criteria are text it should be always be written within quotes.
- You might #VALUE! Error if the given text criteria are of more than 255 characters.
- In SUMIF, the range and sum_range should always be ranges and not arrays.
- In case sum_range is not given, cells of the range will be added which meets the criteria provided.
Recommended Articles
This has been a guide to SUMIF Formula in Excel. Here we discuss how to use SUMIF Formula in Excel with examples and downloadable excel templates. You may also look at these useful functions in excel –
