Excel Tips to Impress your Boss #1 SUMIF
Have you ever used the SUM function on a table of data only to realise that you really need to SUM up some of the data and not all of it. Then you are faced with the laborious task of extracting the bits you want to add up and then using SUM on that. Well never fear cause SUMIF is here.
SUMIF is like Ronseal it does exactly what it says on the Tin. It will apply the SUM function only IF certain criteria are met! (Warning! SUMIF will not prevent your wooden furniture from being damaged by those knocks, scuffs and spills).
So how does SUMIF work I hear you cry. SUMIF takes in three arguments Range, Criteria and [Sum Range].
Range – Is a range of cells you want to check for a match
Criteria – Is the specific match you are trying to find
[Sum Range] – Is the range that you want to Sum
So it would look like this…
=SUMIF (Range, Criteria, [Sum Range])
Let’s look at an example. The other day I wanted to find out how much money I spent on my Wife compared to how much money I spend on my Car. Handily I keep all this info in an excel spread sheet
See in cell F1 the formula a used in cell F2 for finding how much I spent on my Wife. Here my Range is A2:A9 (the Receiver column), my Criteria is “Wife” (so the formula looked for cells with the entry Wife in the Receiver column) and my [Sum Range] is C2:C9 (the Euros column). This formula then added all the Euros that were in the rows with ‘Wife’. Using this and the other formula (see above) I was easily able to find out I spent twice as much on my Car as I spent on my Wife….. Unfortunately my wife read this spreadsheet to so…
… now I sleep in the car…
Well I guess excel isn’t always helpful!
I hope this helped you with your excel experience which you’re sure to excel in. This is goodbye from me, for now, and as always ‘Remember if you’re stuck in excel …Google is your best friend’
Here is a handy way to remember the SUMIF function
=SUMIF (Where you want to check, What it is you are checking for, Where you want to sum)
Authored by “Excel Aidan” a self confessed Excel fanatic and near-do-well funny man. See Aidan’s profile here and email email@example.com if you have a suggestion for our next excel topic.
At Barden we invest our resources to bring you the very best insights on all things to do with your professional future. Got a topic you would like us to research? Got an insight you would like us to share with our audience? Drop us a note to firstname.lastname@example.org and we will take it from there!