Today, I'll be sharing 3 wonderful Excel formulas: SUM, SUMIFS and COUNTIFS.
So let's start with SUM. It lets you sum several numbers easily (with fewer keystrokes than doing a+b+c+d+...)
Here's the formula's syntax: =SUM(Range)
A Range is always in the form of one cell to (:) another cell. In the example on the screenshot above, the range is B2:B7.
This means you are summing the values in cells B2, B3, B4, B5, B6 and B7. The longer, more familiar, equivalent is =B2+B3+B4+B5+B6+B7
And that’s how sum works.
Very easy and helpful. Saves time and keystrokes.
Because of it’s frequent use, Microsoft dedicated a button for it in the Home menu.
Next is SUMIFS.
Here's the formula's syntax:
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Don’t be put off by it’s long syntax. It’s a very easy to use formula and a very useful one too. It lets you add only numbers that belong to a specified category.
If we have a sales report that has names of the salesmen, their region/states, and sales they generated in a particular year. Then the CEO says he is not interested in this long report, he wants to see sales by region only. Are you going get a calculator and start computing for each region? (I hope not).
This where SUMIFS come to the rescue. You can set that you want to sum for sales that occurred in a specific region. Then do for each of the other regions.
Let’s say we have 1000 records (A1:C1001, cells A1, B1 & C1 have field headers). Column B is the Region field. And Column C is the Sales amount field. Our SUMIFS formula for Lagos will be:
=SUMIFS(C1:C1001, B1:B1001, “Lagos”)
And it’s that simple.