The SUMIF function in Microsoft Excel helps to find the sum of values meeting a particular condition. This function is really simple to use and comes in handy when you want to summarize a data based on a particular condition.
In this tutorial you will learn how to use the SUMIF Function in Microsoft Excel with a simple example of 2 shops selling gadgets and we want to Sum only if the gadget sold is Samsung Galaxy S6 tablet.
The Syntax of SUMIF Function
The syntax of SUMIF formula is as follows
=SUMIF(range, criteria, [sum_range])
Range: This is the range of cells where the item that you want the sum for is located. In our case below the range is A1:A9 – since the item names that we want to find the sum for are located in Column A in 9 rows.
Criteria: Here you can specify which item to add up.
Sum_range: This is where the range of values that can be summed up are located. In our case below, the items sold are located in column C.
With this understanding we can remember this formula in the following way:-
=SUMIF(Ranger where the item you want to sum is located, Item name that you want to locate, range that you want to sum from)
How to Use SUMIF Function
Take a look at following table where we have the item names in column A, Shop reference in column B and the number of items sold by each shop in column C.
Now, let us say that you want to find the total number of Samsung Galaxy S6 phones sold. This can be easily done using the SUMIF function:
1. Write the item name Samsung Galaxy S6 in Cell 11.
2. Place the cursor in cell B11 and start entering the SUMIF formula beginning with “=”
3. Excel will automatically provide you the synatx =SUMIF(range, criteria, [sum_range])
4. Choose Cells A1:A9 as the range – the data range where the item that we want to sum is loacted
5. Specify “Samsung Galaxy S6” as the criteria
6. Choose C1:C9 as the sum_range and click enter.
The SUMIF formula will instantly display the number of Samsung Galaxy S6 phones sold in cell B11.
Instead of writing the item name (Samsung S6) you can point to any cell in column A containing the item name. Excel will understand that you want to find the sum for the item located in that cell. In this case the formula will look as below.
=SUMIF(A1:A9,”A6,C1:C9) – note that instead of writing the item name we are pointing to a cell containing the item name.