I use the SUMIF function on Excel almost daily it is so useful and easy to use, however at some point in the past three years I had no idea how to use it.
I am pretty sure I googled it, so let me pass on the How To.
What does SUMIF Do;
This function allows you to add up or total a range of data with conditions.
For example say you have a range of data that shows total sales for the week and by product;
In the above example we want to know the total sales for the day of Apples & Kale the SUMIF function can quickly provide this for us.
The easiest way to use the function is choose the cell you would like the calculation to be in, before this cell before type Apples and then in calculation cell type =SUMIFS(
And select the data range you would like to total, in this example it is column C the net sales price;
Then type a , and choose the cell range of where the IF data is, in this example that will be column B. Where the product name is.
Type a ,
And then either click cell B20 or Type B20 then enter a close brackets ).
The Formula will look like this – =SUMIFS(C2:C18,B2:B18,B20)
Repeat for Kale.
Alternatively if you pop a $ either side of the letter on the cell range this will keep that data range without moving it.
Drag the cell down using the small square at the right hand side of cell C20 and drag down.
This will copy the formula but move the cells down one so it moves the end cell to C21.
As you have used the $ signs those cells will not move.
This can be used in all formulas and is a useful tool.
Let’s use it in another way using this example.
Remove the $ that you have entered around the C cells and add one to the B20 so the formula becomes;
=sumifs(C2:C18,$B$2:$B$18,$B$20)
Then drag from the small square across.
This has now totalled the VAT for the Apples sales for the day.
Alternatively you can head to Formulas from the top ribbon and this find the SUMIF function from under Maths & Trig this then walks you through the formula.
The Criteria will be Apple in this example and the sum range is the net amounts
And that is the SUMIF function, once you have used it three times it becomes really simple to follow and remember and can be used for multiple conditions.
It is more complicated to explain than to do.
