When to use =sumif()

There are a couple of ways to add up your data
The obvious one is =sum()
The other is adding up individual cells, handy if there are gaps between =D4+D8+D12+D16 etc
This second one is ok if you are adding up a few cells but gets very annoying if there are more than 4
Not only that, but errors can occur:
• Putting the wrong reference in
• Clicking on the wrong cell
• Missing cells
• Adding more data & forgetting to add to the sum
• Deleting data & getting the #REF! error


A much better way is to use =sumif()
This lets you select the range you are adding & only totaling the cells that match a criteria
For example
I just recently had a spreadsheet that had 4 different types of data repeating down the spreadsheet for different accounts
They all needed totaling at the bottom
A normal =sum() would not work as this would sum up all 4 for each account & they needed to be separate
So, the client used the second option
Which by the time I got it, already had 12 cells adding per the 4 data types


I had to adjust the accounts, delete a few etc
So the =sumif() is the best function to use
As this is a range based function, it didn’t matter if rows are deleted or added
Plus, I had the confidence that all the data was being captured


If there is not an obvious criteria for the sumif, force one by putting something in another column
I did this for another file by typing an a next to first data type, b & c to 2nd & 3rd
Then used these a,b,c criteria’s when I needed to total up the data in sumif


It is a very handy & often overlooked function which is easier to use than you think

Chishiki

knowledge, understanding, information
Excel Tips

Excel Super-Powers

It seems Microsoft has been inspired by Marvel by creating functionality called POWER It has Power Query, Power Pivot & Power BI All 3 have

Read »

Kaigi o tehai shimashou

let's arrange a meeting