Updated June 12, 2023
Part -15 – SUMIF Function In MS Excel
Download Excel Templates – SUMIF Function In MS Excel
SUMIF Function In MS Excel (without solution)
SUMIF Function In MS Excel (with solution)
Transcript For The Video – SUMIF Function In MS Excel
Video on SUMIF Function in MS Excel –
Let us now calculate the total salary of marketing employees. In this case, we will use SUMIF function in MS Excel, so the way it is written is again = SUM If total only when it needs certain criteria. So let’s look at the syntax bit closely. Here talks about the range, what is the criteria, and what is the Sum range. So if you remember, range and criteria essentially the same, which we used for count IF. So let’s take a range as the same range which we were talking about criteria, I mean the COUNTIF.
What are the criteria is that it should meet marketing, and what is the SUM range? The Sum range essentially is to Sum total to an annual salary of the marketing guys, so this is how your Sumif function in ms excel is written, and we close the bracket, and we press ENTER. So what we find here is that corresponding to marketing, it’s 30,000 that gets added, corresponding to IT, i.e., 50.400 that doesn’t get added. So that’s how it works. It automatically identifies the corresponding entries and sum total, so let’s quickly verify if this number is actually correct by doing manual calculation. So this is marketing G22 + G20 + G16 + G15, then on the top, we have G8, G7, and G4 and what is the SUM total? The SUM total essentially is the same, so SUM if is working perfectly fine. Now let us calculate the total number of employees and total salary for other departments like IT, Finance, and support. For this, we wish to use the same formulas, which is COUNTIF, the full range, and the criteria now note that if we are wanting to copy this formula from marketing to, let’s say to next cell, which is cell D31 what happens is that this criteria C31 are correctly identified because we want select only IT professional but look at this range, what has happened to the range.
The earlier range started from D4, and it went till D22. However, now the updated range because we have shifted from row number 30 to row number 31, it is now bit different. Now if you look at the highlights here, you may be able to observe this full range. This is not the range in which we were kind of talking about, the range should start from D4, and it should continue till D22. So what can we do in this case in order to make our formulas kind of compatible with the other set of copy and paste? What we need to do here is the small correction. The small correction would mean we need to use absolute references, which we have learned in our earlier video. So how you can make use of absolute references? We want this range to freeze and not move as we move the formulas from marketing to IT. So what we can do is, we can insert Dollar sign before D, we can insert dollar sign before 4, we can also insert dollar sign before D again, and dollar sign before 22. So that is how we have now kind of freeze this range, so it will not move so. Let’s try if this is kind of moving or stationary.
Now let’s look at the updated formula. In this case, as you can see, the range in now stationary, and it is not moving, so the formula is working correctly. So let’s complete the whole set of calculations here. In case of finance and support so what we see is that there are four employees in finance 4 in, support 4 in IT 4, and 7 in marketing, so let’s apply the same kind of terminology and formulas In SUMIF function In MS Excel. We wish to copy and paste this formula so that we don’t have to do retyping all and all again, so there are two ranges; this one which is initially set of range the second one is sum range, so what we must do is to freeze this range. Now I am going to teach you a shortcut in order to insert the $ sign. So the shortcut is basically available in the function key, i.e., F4, so on the top, you will find there is the command F4 in order to insert dollar sign between, let’s say, D4. Just select your cursor in between D4 and press F4 from the function key, so what will happen is it insert dollar sign before D and Before 4 automatically. Likewise, if I have to do it before D22, I’ll press F4, likewise before G4, and G22; remember that I don’t want the C30, which is pointing to marketing to be an absolute reference. Because I want this to be moving reference so, this is a normal reference in our case, so what we will do is now, just copy this formula and paste it across.
So we can see that in our case, it is working perfectly, so if you see support, the total salary that is basically taken is in cell G33. Now let’s say if you want to do another set of calculations total number of employees who are male and total salary, its again a quick revision. Let’s do it COUNTIF, and this time I don’t want to count the department, but I would like to count the gender, so gender range would be from E4 to E22, and the criteria is male and bracket close. So what we have is there are 11 employees who are males, and if I want to copy and paste the formula again in this female cell, I must have a dollar sign in between E4 and E22, and I’ll press F4 as a shortcut and F4 again as a shortcut for E22 and I can copy this formula and paste it again. So there are 8 female employees and 11 male employees. Likewise, let’s do quick sum total of the total salary, so here, I will use this SUMIF function in MS Excel.
What is the range? The range here on which the criteria needs to be employed is E4 and E 22. What is the criteria, the criteria are those belonging to the male, and what is the sum range? The sum range is the range of total annual salary, so this is what we take and close the bracket and ENTER. So the salary essentially taken by male employees is given in cell G35. Likewise, if I have to copy and paste this formula here, I’ll be using F4 again, so relatively quickly this time F4, F4, F4, D22, and copy this and paste it across in female. So this is how, you know, we can work along with SUMIF function in MS Excel and COUNIF functions. This is how we can use SUMIF Function in MS Excel.
Recommended courses
This article has been a guide to the SUMIF Function in MS Excel, which is used to add a set of cells only when the condition is TRUE. These are the following external link related to MS Excel, so go through the links for more details:
