You can use the SUMIF function in Excel to add or sum up a group of figures that meet a certain criteria. Say for example that you want to add up all of the sales that occured on a Wednesday. Or maybe you needed to sum all of the sales that were made in a certain section or a certain region.
- Download and Open the Use of the SUMIF Function in Excel 2016 exercise file.
- If necessary click on the Enable Editing button at the top of the Excel window.
We’re going to work out how much money we spent on each item in the list. If you look to the right in cells H2:H5 you can see the list of shopping items.
- Click in cell I2, then click on the Insert Function Button.
- In the search for a function box type SUMIF; then click Go. You will then see the list of functions listed below. You should see the SUMIF function at the top of the list.
- Double click on the SUMIF function be careful not to double click on the SUMIFs function. The SUMIFs function is slightly different and we are not going to discuss it in this training guide. You can alternatively click once on SUMIF and then click OK.
- In the Function Arguements box that appears click in the range box then using the mouse select cells B2:B199. Then click in the Criteria Box to select the criteria for the SUMIF.
- Now it’s time to select the criteria that we want to check against the range. While your cursor is flashing in the Criteria box click once on the work Eggs in cell H2. Note that the word Eggs should appear to the right of the Criteria box within the function arguments window.
- Click in the Sum Range box so that we can select the range of cells that we want to sum, or add up.
- Using your mouse select the range of cells E2:E199. You will need to click and hold your mouse on cell E2 then move the mouse down to cell E199. It can be a little fiddley. What you can also do is to click on cell E2 then, using your keyboard, hold down Ctrl and Shift. Now, while keeping the Ctrl and the Shift key held down, tap the down cursor arrow on the keyboard. You should have now selected the same range of cells. I think you’ll agree, if you managed to get the last option to work, it’s a lot faster.
- Nearly done, just one more thing. We need to fix the range cells by creating absolute references, so that when we Autofill the formula the range cells don’t relatively reference. That is they should slip down. Try the following:
- Highlight the Range cells B2:B199 back in the top box. Then using the keyboard press F4 (Function 4). You should now see little dollar symbols before each column and row. The Range box should now read $B$2:$B$199. If you want to know more about how this works, then I would suggest going through the Absolute references video on the Computer Tutoring website.
- Follow the same procedure with the Sum Range box so that instead of E2:E199 the box should read $E$2:$E$199
- Have a look at the SUMIF box it should look like:
- Now click on OK and you should see the result: £187.06
- Now finally you can AutoFill down by using the small green dot at the bottom right corner of the cell with the result. In the above example it’s the cell that has the value £187.06. One little tip: You can double click the AutoFill square to fill the function down relative.
- Bask in the glory that is the SUMIF function. Remember to pat yourself on the back and check that your sheet looks like:
So there you go. SUMIF is a well used function, that is many use it every day to do what you have just done. Now is the time to practice this again if you want to remember. Also it might be an idea to consider the following questions:
- In what way could I use the SUMIF function in my everyday life?
- How could the SUMIF function work in conjunction with other activities at work?
- Apart from the SUMIF function what other similar functions could you take advantage of?
- SUM Function – (Back to basics)
- Index Match function (the super powered VLookup)
- COUNTIF using 2 Criteria (A little more advanced from our sister website).
Now if you want to be able to use the SUMIF function across multiple sheets then you can do no worse than check out this SUMIF Excel across multiple sheets. It might be just the thing that you’re looking for.