MVP #31: SUM, SUMIFS and COUNTIFS

I can't believe that I'll be busier being on my own than in my previous job. And keeping my one post a day is becoming a race against time. A day, for me, now has 24 very short hours.

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.

Next is COUNTIFS.

Here's the formula syntax:  = COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

It’s a like SUMIFS. Similar syntax, except that COUNTIFS won’t sum numbers for you. It will give you a count of all the records that meet you criteria.

For example, the CEO wants to also know the number of Salesmen in each region.

For Lagos region, our COUNTIFS formula will be

=COUNTIFS(B1:B1001, “Lagos”)

The peculiar thing about COUNTIFS is that you will only provide the Criteria Range and desired Criteria. That’s why our formula has got two parameters. We want to know how many records belong to Lagos region.

Now you know 3 cool Excel formulas (I'm sure a lot of you already knew them).

And I'm sorry I didn't include my usual flood of screenshots. I'll make up for this in my next Excel post, or the one after that. Thanks!