Excel SUMPRODUCT an Alternative to SUMIFS

Excel’s SUMPRODUCT function has some handy uses for Excel 2003 users who desperately want the SUMIFS, COUNTIFS or AVERAGEIFS functions (the *IFS series of functions).

And if you’re an Excel 2007 or 2010 user keep reading because there’s a cool way to use SUMPRODUCT which gets around the limitations of Excel’s *IFS series of functions.

You might like to download the workbook and follow along, reverse engineer the formulas or practice what you learn.

First of all the limitations of the *IFS series of functions:

The *IFS functions only work with AND logic between the criteria.

For example: SUMIFS(sum the range A if range B = criteria 1, AND range C = criteria 2, AND range D = criteria 3…..)

But with SUMPRODUCT we can specify OR logic as well as AND logic in a SUMIF style of calculation.

For example: SUMPRODUCT(range A, if range B = criteria 1, OR range B = criteria 2, AND range C = criteria 3…..)

Note: the configuration of the SUMPRODUCT formula above is for illustration purposes only, the actual syntax of the SUMPRODUCT function is different. See below.

Excel SUMPRODUCT Function Examples

Our data below is laid out in a table that has been converted to a range. Each column has a named range the same as the header in row 1. Therefore in my examples I will refer to the column range G2:G207 as the named range ‘solarSystem’ and so on.

How to use the SUMPRODUCT function Instead of SUMIF

Using the data above let’s say I want to sum the Volume for the Endrulf solar system. My SUMPRODUCT formula would look like this:

=SUMPRODUCT((Volume)*(solarSystem=”Endrulf”))

Ok, so I could do that with a SUMIF, but what if I had more than one criterion?

How to use the SUMPRODUCT function Instead of SUMIFs

This is for you if you’re stuck with Excel 2003!

Let’s say I want to sum the volume for Endrulf solar system AND IF Jumps = 6

=SUMPRODUCT((Volume)*((solarSystem=”Endrulf”)*(jumps=6)))

How to use the SUMPRODUCT to SUMIF with OR as well as AND logic

=SUMPRODUCT((Volume)*((solarSystem=”Rens”)+(solarSystem=”Endrulf”))*(jumps=6))

The above formula reads:

=SUM ((Volume) IF ((solarSystem=”Rens”) OR (solarSystem=”Endrulf”)) AND (jumps=6))

In SUMPRODUCT functions you can employ the AND logic, and OR logic using the * and + symbol:

• When the multiplication symbol * is used it reads ‘AND’.
• When the plus symbol + is used it reads ‘OR’.

How the SUMPRODUCT Function Works

Firstly let me say to all those ‘Excel Gurus’ reading that yes, there are many ways to structure a SUMPRODUCT function but to avoid overwhelm I am using what I think is the easiest to understand, and since they all result in the same answer (albeit some may be quicker for Excel to calculate) I figure this is a good way to start getting your head around the SUMPRODUCT function without scaring people away.

In the SUMPRODUCT function Excel is testing for TRUE or FALSE answers, and in Excel the numeric equivalent for TRUE is 1, and for FALSE it is 0.

These are known as Boolean terms….you may remember learning them at school and, if you were like me, you’d have thought ‘why would I ever need to know that’ and promptly filed them away in your memory along with Quadratic Equations and SIN, COS and TAN.

Not to worry, I’ll remind you how they work as they’re really quite straight forward…but you might like to get a snack like the apple at the top of this post  (also known as a ‘brain booster’ at my 5 year old’s school).

So, using this formula:

=SUMPRODUCT((Volume)*((solarSystem=”Rens”)+(solarSystem=”Endrulf”))*(jumps=6))

Let’s look at our data and take row 4 below as an example and apply the formula:

= 13,417 * 1 * 0 = 0

Explained:

SUM Volume 13,417 * 1 (because G4=Rens therefore = TRUE, which = 1) * 0 (because H4=7 therefore = FALSE which = 0)

Or if we look at row 6:

5,217,955 * 1 * 1 = 5,217,955

How to use the SUMPRODUCT to COUNT with multiple criterion

Using my example; if you want to use SUMPRODUCT to count values based on multiple criterion using AND or OR, you would simply drop the Volume component of the formula like this:

=SUMPRODUCT(((solarSystem=”Rens”)+(solarSystem=”Endrulf”))*(jumps=6))

How to use SUMPRODUCT to AVERAGE with multiple criterion

To calculate the AVERAGE we simply divide the total amount by the COUNT of the total volume like this:

=SUMPRODUCT((Volume)*((solarSystem=”Rens”)+(solarSystem=”Endrulf”))*(jumps=6)) / SUMPRODUCT(((solarSystem=”Rens”)+(solarSystem=”Endrulf”))*(jumps=6))

SUMPRODUCT Using Dates as Criteria

Say I wanted to add the criteria for the month of January 2011 instead of the ‘jumps=6’:

=SUMPRODUCT((Volume)*((solarSystem=”Rens”)+(solarSystem=”Endrulf”))*(Date>=DATEVALUE(“01/01/2011″)*(Date<=DATEVALUE(“31/01/2011″))))

Remember the Date could also refer to a cell that contained the date, or the date serial number:

Using cell references for the date (cell L12 contains 1/1/2011 and cell M12 contains 31/1/2011):

=SUMPRODUCT((Volume)*((solarSystem=”Rens”)+(solarSystem=”Endrulf”))*(Date>=L12)*(Date<=M12))

Using serial numbers for the date:

=SUMPRODUCT((Volume)*((solarSystem=”Rens”)+(solarSystem=”Endrulf”))*(Date>=40544)*(Date<=40574))

SUMPRODUCT as an Alternative to Helper Columns

What say we wanted to know the sum of the Volume x Price. We could insert a formula in column J that calculated Price x Volume for each row of data, and then sum column J to get a total, or we could use the SUMPRODUCT function like this:

=SUMPRODUCT(price,Volume)

Remember: ‘price’ is the named range for column A and ‘Volume’ is the named range for column D.

The beauty of the SUMPRODUCT in this calculation is you can achieve the same result in one cell that would otherwise take up a whole column.

Quick Recap on the Rules

In SUMPRODUCT functions you can employ the AND logic, and OR logic using the * and + symbol:

• When the multiplication symbol * is used it reads ‘AND’.
• When the plus symbol + is used it reads ‘OR’.

Tip: if your formula results in a zero and you know it should be >zero then you either have an error in your data, or you have an error in your formula.

P.S. If you’re wondering what the data is in the example, it’s a data dump from EVE which is a game Phil plays where he flies fantasy space ships in a fantasy galaxy, fighting fantasy baddies. Just goes to show some men never grow up!