Today we will learn How to use SUMIF Function in excel. I recommend you to download the practice file (download link at the end of this post) to follow along with me.
We will be learning:
What is SUMIF Function in Excel?
How do I use SUMIF Function?
How do I use SUMIF Function with dates?
What is the difference between SUMIF and SUMIFS Function?
How to use SUMIF with Texts?
How to use SUMIF with Cell colors?
How to use SUMIF with VLOOKUP?
Now Open Your downloaded file with excel and follow me.
Sumif function is very powerful function because it is used to sum the values on conditional basis.
If the set conditions are matched then only it will sum. Otherwise it will not sum.
The best part is that you can set your own condition in the formula. Its called criteria.
To express the SUMIF function in one line, Then it will be like,
IF the condition is matched then do SUM.
We will learn it step by step with examples.
What is sumif in excel?
SUMIF Function in excel is the combination of two functions which are, SUM Function(Mathematical) and IF Function(Logical Function).
But before jumping directly into SUMIF function, let’s go through SUM Function very quickly.
I assure it You will find it more interesting.
We all know how sum function works.
There are three parameters in SUM Function on which it works.
Input Value ( Cell A1, Cell D4, Cell G7,…..)
Cell Range (A1: A18)
We will discuss here Cell Range parameter of SUM Function Only.
It sums the range which is selected in its argument. SUM(A1: A18).
SUM Function is very simple It has only one argument which is Range from the Table which you want to SUM.
Expression of SUM Function is SUM(Cell Range) that is SUM(From:To), Here From and To is the range you set of any table.
The SUM Function can be used Vertically and Horizontally both, But not diagonally if you are applying a cell range.
Now Let’s go through IF function quickly.
IF is a logical function which is used to compare the data on a set criteria.
For Instance, If there is X exist in table then return Yes, If not exist then No.
It results in TRUE and FALSE in excel sheet.
As I Said Before that SUMIF Function is the combination of both SUM- The Mathematical Function
IF- The Conditional Function.
Here, We SUM only Those values which fulfills the IF Criteria.
How do I use SUMIF Function in Excel? (sumif with example)
The Formula expression for SUMIF is: SUMIF(Range, Criteria, Sum_Range)
There are Three arguments in SUMIF Function.
Lets understand with the below example.
As you can see in the above example we have sales data of various brands
I want to know the sum of sales quantity of a specific brand, say Lenevo.
As You can see that lenovo is repeated several times with different quantity.
To Find the Total Sales of Lenovo, I will use SUMIF Function.
Let apply the function here.
Here A3:A18 is the Range where Lenevo Exists,
D1 is the Criteria (Lenevo in our case)
and B3:B18 is the Sum_Range which we want to SUM.
Here, We are saying,
Select the Lenevo in A3:A18 then add the Total values of cells which are next to every lenovo cell.
Here is Your Result.
As You can see There are Three Arguments in SUMIF Function:
Range: Where is the criteria in a table? (our case it is A3:A18)
Criteria: What is the criteria? (we want to know the quantity of Lenevo)
Sum_Range: What do You want to Sum? (Sales Quantity Range)
Using SUMIF Function with Absolute reference in Excel
Here I have to apply $ Sign before every cell address. It is called Absolute reference. If we are not going to apply then your table range will skip downward whenever you copy and paste the same formula in the next cell. (Don’t Forget to change the Criteria in copied cell)
Consider Dollar $ sign as a Padlock which locks the cell range in all the corners.
Here is the example when I did not use absolute reference to lock the cell range. As you can see the Selection is skipped.
See How A3:A18 skipped to A7:A22
And Here is the result of SUMIF Function with Absolute Reference.
This time selection Table is not skipped. Selection Range A3:A18 remain unchanged.
How do I sum specific cells in excel?
Let’s assume that you have a data where there are blank cells. Now You want to sum those specific blank cells only.
Here is the quick example. The Blank Cells are Highlighted in Grey Color in A Column Range.
As you can see If you want To sum the data of blank cells only you have to replace Criteria with “”. (Two double quotes without space)
=SUMIF(A3:A18, ““’, B2:B18)
As you can see in the above expression we replaced criteria with “” in the formula.
To SUM Cells which are not blank use “<>” in criteria.
=SUMIF(A2:A18, “<>”, B2:B18)
In the same data you want sum those cells which are not blank then use “<>” in criteria.
How do I use Sumif with dates?
Yes, You can use SUMIF Function with dates. It’s quite easy. There are two ways you can use SUMIF with dates.
Find the SUM for specific dates
Find the SUM between selected dates.
Find the Sum of Specific Dates:
As you can see in teh A column there are several dates written, Now I want to know The Total Sales of Mobile Phones sold on 01 January 2019.
In The Cell E1, I wrote the date 01/01/2019 in MMDDYYYY Format.
So expression will be =SUMIF(A3:A18, E1, B3:B18)
The result will be The Sum of Total sale for the date 01-01-2019.
Here is the result:
Note: You have to use date in criteria (mmddyyyy) to find the sum of specific dates. Rest of two argument in the formula will remain unchanged.
Find the SUM between selected dates.
It is going to be tricky, If you don’t get it in the first time then skip to the next paragraph.
You will be Subtracting one SUMIF Function with another SUMIF Function.
We will use Date(mmddyyyy) in Criteria.
Lets understand it with the following example
As You Can See in the below example there are dates for the month of January 2019 on which mobiles were sold in different quantity.
I want to find out How many mobiles were sold From 1st January 2019 to 7th January 2019.
To find How Many Lenevo sold between two dates we will use SUMIF Function:
We will Find the difference between Two dates.
Here we will subtract SUMIF Function of End Date by SUMIF Function of Start date.
SUMIF End Date Minus SUMIF Start Date
The Expression will be:
As you can see in the above formula, Range and Sum_Range has remain unchanged in both.
There is a change in Criteria. We selected F6 (End Date) in First SUMIF and F2 (Start Date) in Second SUMIF.
We have also added More then Equal to(<=) Operator before the cirteria of SUMIF End date and Less then Equal To (=>) before Criteria in SUMIF of First Date.
It is simply picking the Total phones sold from 1st of January to 7th of January then subtracting it with total sold between given dates.
This is how you use SUMIF Function with Dates.
However, If You are going to use SUMIFS then it will be more simpler. We will also learn it after few minutes. That how to use SUMIFS between dates.
How many criteria can SUMIF have?
SUMIF Function can have only one criteria. If you supply More than One Criteria then it will not work properly. But If you want to use SUMIF with multiple criteria the you have to use SUMIFS Function in excel.
SUMIFS Function allows You to do sum on multiple criteria.
What is the difference between sumif and Sumifs?
The major difference between SUMIF and SUMIFS is the Number of criteria you can have.
The second difference is that SUMIFS Formula starts with Sum_Range.
As I Said before, You can apply only one Criteria with SUMIF and If You want to apply data to be filtered on multiple criteria then you can use SUMIFS.
With SUMIFS Function You can apply 2 Criterias in a single formula.
That Means You can SUM the data with applying 2 conditions in a table.
However, Pivot table can be used to ease the process if you have to apply more then 5 criteria.
Now we will learn how to use SUMIF with multiple criteria using SUMIFS function.
Let’s get back to our previous example.
I added one more column for zone here.
There are Four Zones North, West, East and South.
Now I want to know How many Lenevo mobile phones were sold in North Zone?
As you can see we need to know the sale of Lenevo in North Zone.
So, we will use SUMIFS Function.
Here is How to use SUMIFS Function Step By Step.
Write SUMIFS and Select the Sum_Range you want to SUM.
In our case it is C3:C18
Select the Criteria_Range 1. In our case the First criteria is Zone, So we will select entire Zone Column.
Now it asks for criteria1, which means for which particular zone you are looking for. We will write North here.
Select The Brand Name range in Criteria_Range 2.
Select The Brand Lenevo and Press OK Button. You will finally get the Total number of Lenevo Mobiles sold in North Zone.
Can you Use Sumif by cell colors?
Yes, You can use SUMIF by cell colors not directly but with a quick trick. There is no direct way to apply to use SUMIF by cell colors. You have to assign new helper column to use SUMIF by Cell colors.
We Will Continue with another example. As you can see in the example we have assigned numbers to specific colors in helper column.
We have assigned numbers to colors.
1 for Green
2 for Grey
3 for Purple in the Helper Column Range A. You can change the font color of Helper column to white or you can Hide the Helper Column. It’s Your choice.
We are changing the font color of helper column to white.
Now I want to sum the values which are Green in Color.
To Sum the values which are Green in color, apply SUMIF(Range,color number, Sum_Range)
In the Range I will Select A3:A16.
In the criteria I will Type 1 as 1 is assigned for green and finally we will select the Sum_Range C3:C16
The Formula expression will be: =SUMIF($A$3:$A$16,1,C3:C16)
Here is the Final result:
Note: I Hid the Column A and Changed the Font Color to White. (The Trick for which we spoke earlier)
It will return the Sum of values which are green in color.
Can we use SUMIF Function with texts?
We have done it earlier before. Using SUMIF with texts is very easy. SUMIF does not bounds you to use it with numbers only.
You can set the criteria to any Speicific Number, A Date or Any Text String.
We will continue with our previous example. As You can see there is another column of Build quality grade for all mobile phone brands.
These Build quality grades are in Texts A, B.
If I want to find the mobile phones which have A quality grade then I will be selecting “A” in the criteria of the formula.
The expression of the Formula will be:
How to use SUMIF with VLOOKUP?
It is going to be a very important part of this tutorial so pay attention.
I am assuming You already know how to use VLOOKUP Function. If you don’t know how to use Vlookup then first Learn How to use Vlookup in excel.
As you can see in the below screenshot. We have Three Tables.
In the First Table have Product Code and Quantity.
In the Second Table have Brand Name and Total Sale (Which we have to findout)
In The Third Table we have Product code and Brand Name
Now We need to find the sale of Lenevo from the first table.
The problem is that there is no Brand Name column in the first table.
The Product code and Brand Name is given in the Third Table.
So we have to apply VLOOKUP to bring the Product code from the Third Table.
I am looking for E3 cell into the third table to know the Product code.
Formula expression will be:
Now we have our Product code which is 1234.
Here is the magic,
This 1234 is our criteria to bring the sale of every 1234 from the Table 1.
We will use this product code as Criteria for SUMIF Function.
Let’s apply SUMIF Function in the same column.
The Expression is:
B3:B18 is the Criteria_Range
VLOOKUP(E3,H3:I6,2,0) is our criteria
C3:C18 is our sum-range.
As you can see now it has given SUM the total number of Lenevo in sold.
This is how you apply SUMIF with VLOOKUP.
Download Practice file For SUMIF Function From Below:
This is How you use SUMIF Function in excel. If you face any error while applying the any of this function then make sure read about excel errors from this post.
We have learnt so far the basics of SUMIF Function to Integration with VLOOKUP Function in Excel. But this is not the only limit. You can use the SUMIF Function with HLOOKUP and INDEX- Match Function as well.
I hope You have liked the post. To master this function and integrating with other functions, You have to practice it with again and again with your own task related examples. The more you practice the more you will become efficient in using SUMIF Function in Excel.