Part -15 – SUMIF Function In MS Excel
Download Excel Templates – SUMIF Function In MS Excel
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 its 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 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 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 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 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
This 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: