## Excel Tip: Adding up columns based on multiple criteria (the SUMIFS function)

**From the Not Just Numbers blog:**

Before getting into today’s post I want to point you to an excellent free Webinar being offered (for a limited time) by Mynda Treacy, entitled “Creating Excel Dashboards“. Mynda is a real expert on Excel Dashboards and her training materials are always excellent. You can register for the webinar here.

I realised the other day that I had never covered one of my most used functions on this blog – SUMIFS. I have covered its predecessor, SUMIF, as SUMIFS has only been available since Excel 2007.

Although SUMIF is still available in later versions of Excel for compatibility purposes, it is essentially redundant, as SUMIFS does the same thing, plus a lot more.

Let us look at an example of some sales data (see left).

Say we want to know how much Mary’s sales were, or how much Sarah sold in the East Region, or even how much Ben sold in the North region in the month of January.

SUMIFS can do all of these.

The syntax for SUMIFS is as follows:

=SUMIFS(SumRange,CriteriaRange1,Criteria1,[CriteriaRange2],[Criteria2]…..)

You can have as many pairs of CriteriaRange and Criteria as you need. The function works as follows:

SUM SumRange where CriteriaRange1 = Criteria1 and CriteriaRange2 = Criteria2 etc. for however many criteria you have.

For all of the examples above the SumRange will be D2:D21, as this is the range we want to sum, subject to our criteria. We will look at how we construct the rest of the formula for each of our examples above.

**How much did Mary sell?**

Here we only have one criteria:

CriteriaRange1 = C2:C21

Criteria1 = “Mary”

=SUMIFS(D2:D21,C2:C21,”Mary”)

returns £16,853.

**How much did Sarah sell in the East Region?**

This time we have two criteria:

CriteriaRange1 = C2:C21

Criteria1 = “Sarah”

CriteriaRange2 = B2:B21

Criteria2 = “East”

=SUMIFS(D2:D21,C2:C21,”Sarah”,B2:B21,”East”)

returns £1,085.

**How much did Ben sell in the North Region in the month of January?**

This time we actually have four criteria:

CriteriaRange1 = C2:C21

Criteria1 = “Ben”

CriteriaRange2 = B2:B21

Criteria2 = “North”

CriteriaRange3 = A2:A21

Criteria3 = “>=”&DATE(2016,1,1)

CriteriaRange4 = A2:A21

Criteria4 = “<=”&DATE(2016,1,31)

There are two elements to these last two criteria that need further explanation.

The first is that if our criteria is anything other than equals, we need to include the criteria in inverted commas, for example “>23”, or “<=15”, to make it a string. If rather than 23, we wished to refer to a cell (say G5) we can use the ampersand (&) to join two strings together, e.g. “>”&G5.

The second is that if we wish to refer to a date directly, we need to refer its sequential number which we can calculate using the DATE function. The three arguments for the DATE function are Year, Month and Day, so to get the date sequence number for 1st January 2016, we can use DATE(2016,1,1). Note that if we entered 1/1/2016 in cell G5, we could just use “>=”&G5 for Criteria3, as the cell value when you enter a date, is its date sequence value.

Our function is therefore:

**If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies “The 5 Excel features that you NEED to know” and “30 Chants for Better Charts”.**