Excel SUMPRODUCT an Alternative to SUMIFS

The Excel 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 it which gets around the limitations of Excel’s *IFS series of functions.

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.

Please enter a valid email address.

You might like to download the workbook and follow along, reverse engineer the formulas or practice what you learn. Note: This is a .xlsx file. Please ensure your browser doesn't change the file extension on download.

Note: the configuration of the formula above is for illustration purposes only, the actual syntax 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 SUMPRODUCT Instead of SUMIF

Using the data above let’s say I want to sum the Volume for the Endrulf solar system. My 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?

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

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

=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 it 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 it 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).

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 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!

Comments

In a comment from April 8, 2013, Bob Phillips noted this nuance in a reply to Justin, but the “why” wasn’t really explained. As Mynda has shown, a + is used for OR conditions, but if those conditions are being evaluated on different columns, then it’s possible for two or more of them to be true, causing the 1 (TRUE) results to add up to a value greater than 1. This would obviously ruin the count/sum trick, so a SIGN should be used to address that potential.

Say we needed to count the number of orders where bid = Sell or solarSystem = Rens. We would not want to double count orders that are both Sell and Rens, so the safest formula would be as follows:

is there any function or formulas to find out the name of the centre where pt’s surgery was done.
fox ex. a pt visited at centre A but was operated in centre B. now I have find out the total sum by using sumproduct or sumifs function for same I want to know in which centre pt was operated from a database.

Hi,
Can you prepare a sample file with your data structure? This way I will be able to give a personalized answer. You can use our Help Desk System, to upload the sample file:https://www.myonlinetraininghub.com/help-desk
Cheers,
Catalin

To practice sumproduct function, I set up the same the table you are presenting here. I created range name for the columns and copied the same formula you have up here. for any reason, I am getting VALUE error. Can you please explain where I went wrong that I am facing this error?

Hi, this is nice illustration of sumproduct. But, suppose, i am having abcd columns. B and d contains the amount to be totalled, based on a and c, which contains the codes for those amount. I want to use this formula, to sum all the identical matches in a and c column which contains some specific codes.
if suppose, a1, a5, c2,c13,a12 has the same code, say iia, then i want to sum up the amount in b and d columns only matching the code in a and c. how to go around to work this. Expecting your reply, as promised above in your link to comment

I am struggling right now with OR in different columns, i.g:
A B C
$10 1 1
$20 5 2
$15 2 1

I´ll need to sum all the money with B < 2 OR C < 2, so I´ve tried the following:
SUMPRODUCT((A1:A3)*(B1:B3<2)+(C1:C3<2)), that means:
SUM (MONEY IF (B<2) OR (C< 2)), so it will sum the first row and third row, that means $25, but I always get $12.

The OR operation is designed to allow multiple criteria in the same column. Once you start referencing criteria in other columns it only works if both criteria cannot be true at the same time which is not the case for the $10 amount where both columns B and C are less than 2.

Hi,
I have been struggling with the below, I think SUMPRODUCT might help, but I am unable to make it work, please suggest:

I have an employee database with salaries in multiple currencies. I need to classify salaries into fixed bands A, B, C, D. Further, the bands are different for different currencies. As of now, I have 5 currencies, so the IF statement has become a unwieldy.

thanks for quick the reply Mynda. Ive also sent the worksheet via helpdesk.
The problem basically is to use the sumproduct function in excel to add multiple columns with reference to multiple criteria in multiple columns. A rough example is given below:

i want to sum all the weight columns which are with reference to specific colors in all the color columns.
For example if i wanted to find out the total weight with respect to the colour “Blue” the desired result should come up to be 476 that is adding the values 23+272+65+89+27. similarly if i wanted to find out the total weight with respect to the color “white” the desired result should be 328(280+48), adding the corresponding values in weight column.
what would be the required sumproduct formula for this situation?

You can use this SUMPRODUCT formula where your data above is in A1:H4:

=SUMPRODUCT(--(A1:G4="Blue"),(B1:H4))

The logical test (A1:G4=”Blue”) checks for Blue in columns A:G.

The double unary, that is the two minus signs before the logical test –(A1:G4=”Blue”), convert the TRUE/FALSE results into their numeric equivalents of 1 and 0.

So your formula looks like this after the logical test:

=SUMPRODUCT(({0,0,0,0;0,0,1,0;1,0,1,0;1,0,0,1}),(B1:H4))

Because the range to be summed (B1:H4) is offset by 1 column, i.e. it starts in column B as opposed to column A like the logical test, the values form an array that matches the test with the corresponding value like this (note: the two arrays are still the same size even though they are offset):

Can you also please demonstrate how can we use SUMPRODUCT for getting the top 5 with multiple critera’s for e.g.
I need to know the sum of the top 5 Volumes for SolarSystem ‘EndRulf’ and jumps = 6 (considering there are 2,3,4,5.. jumps)

Could you please help me? I have used SUMPRODUCT in a 2007 sheet as the file has to be used on a PC which has Excel 2003. However, I keep on getting the #NAME! error and, for the life of me, I can not see why. Are you able to see what is wrong with

I’ve been playing with the fomula for a bit and kind of got it figured out but when I add more rows of data to imput it is not picking them up even though the data is in the ranges. On one worksheet is a log where I am entering the data as it comes in. On the next worksheet is a summary that spreads the data into groups that are easier to compare and figure out issues/problems. Right now my formula looks like this “=SUMPRODUCT((‘2013 Crane Repair Log.xls’!Date>=B19)*(‘2013 Crane Repair Log.xls’!Date<=C19)*('2013 Crane Repair Log.xls'!CraneNumber="15")*'2013 Crane Repair Log.xls'!Value)" Date CraneNumber and Value are all ranges I've created. Please help!

Hi,
I have a data where i have put the date like 01.02.2011,13.02.2012.
but when i am going to apply date formula then i am getting 01 and 13 as a month but i want 02 as a month so please give me a formula to apply here.

Wonderful explanation, thank you! I literally spent days searching for the right formula for my spreadsheet and this is the only site which made me understand why SUMPRODUCT would work, instead of copying/pasting formulas found online.

I want to see how much I’m spending per budget and per week (for instance Expenses from 1 Jan-7 Jan) so I used:
=SUMPRODUCT(amount,(budget=”Expenses”),(date>=DATEVALUE(“01/01/13”)),(date<=DATEVALUE("07/01/13"))) but I keep getting 0 instead of 5 as a result. Would you have any advice on what I'm doing wrong?

Hi thanks for the useful info above. I would be grateful if you could help me with my following query:
i have text name in Column A and i want to sumproduct values in column B & C with reference to specific names under Column A. Is this possible through sumproduct formula ?

I dont know how should i express my gratitude to you. The formula really works and this is a simple solution to my complex problem. I am amazed on how do you extend your support to someone, whom you dont even know!!! Thanks for your assistance, god bless you !

Thank your for your answer.
I discover myself, today, the solution:
=SUMPRODUCT((Volume)*(ISNUMBER(SEARCH(“E”, LEFT(solarSystem, 1))))*(jumps=6))
But it is wrong this: =SUMPRODUCT((Volume)*(ISNUMBER(SEARCH(“E*”, solarSystem)))*(jumps=6)) because function ‘SEARCH’ search for character ‘E’ in all word, not begin with character ‘E’.

Can you explain me, please, why when evaluate, for example (jumps=6), sometimes return a list like {1,0,1,0…} and sometimes return a list like {TRUE, FALSE, TRUE, FALSE…}.

When SUMPRODUCT evaluates the jumps=6 criteria it returns an array of TRUE’s and FALSE’S. In Excel a TRUE = 1 and FALSE = 0. The multiplication before the argument *(jumps=6) coerces the series of TRUE’s and FALSE’s into 1’s and 0’s.

The multiplication does the same as the double unary in this formula –ISERROR(SEARCH(“Rens”, solarSystem)))

Please send your file to HELP DESK so we can understand what you are trying to do.

My apologies I wasn’t thinking of a SEARCH function when I said you can use equal(=) and asterisk(*) to simulate a LIKE function in
programmming. Anyways, SEARCH function don’t need asterisk or any wildcard character like a question mark(?) for it to function as it does.
It’s like a ‘LIKE’ function only within a TEXT.

On this note, I am confused. Why would you want to simulate an “E*” wildcard search?
Are you trying to validate whether a word begins with a letter E?
You could just use LEFT(Word,1)= “E”.

perhaps a formula like this:

=SUMPRODUCT((Volume)*(LEFT(solarSystem,1)="E")*(jumps=6))

Anyway, I’m still not quite sure as to what you really want here. So might as well
send your file through HELP DESK.

Hi Minda,
Very nice and very useful.
Thank you very much for explication and for all hard work.

I wonder how can I implement a condition “like”. It is possible?
For example: =SUMPRODUCT((Volume)*(solarSystem like “E*”)*(jumps=6))

I discover another useful criteria; if you wont to skip some records:
=SUMPRODUCT((Volume)*(–ISERROR(SEARCH(“Rens”, solarSystem)))*(jumps=6))
In this example the sum skip the records “Rens”.
I hope this help.

Anyways, in a formula level I don’t think you can use ‘Like’ like
you can use an ‘And’ or an ‘Or’.

With you asking that, I suppose you know about programming like VBA.
Well, it’s where you can use the operator ‘LIKE’. However, in a formula level the
combination of an equal sign (=) and an asterisk (*), will give
you the effect of a like operator.

So why don’t you send your file and let us see what you want to do so we can
help via HELP DESK.

I would like to point out in this second formula that you could have not meant AND It’s clear that it’s an OR
because you can’t have 4 conditions on two parallel cells being evaluated.
hence; OR(AND(r<>1,r<>2),AND(r<>2,r<>4). So it’s a plus(+) and not an asterisk(*)

I hope you’ll like it.

The logic is simple. I added first all in row 1. So the total is 14.
Then,
I used SUMIFS and SUMPRODUCT respectively to get the supposedly numbers to be excluded
and deducted it from the total.

Thanks, a very informative explanation. One question, lets say I have to sum a range based on thee AND statements and one OR, for performance would it be better to use the SUMPRODUCT as you describe, or to add two SUMIFS?

Hi
Very well explained. i only use Excel once in a while, and many features get lost over time. So your kind of assistance is a great help, when need arise.
regards
Otto Nielsen
Denmark
PS: And I am human …I think

While both formulas are valid, I’ve found that using Elton’s formula can be useful where the range you are summing contains text (e.g. headings). This can be useful when you are progressively adding to a dataset and so want to sum whole columns.

For example:

=SUMPRODUCT((D:D)*((G:G=”Endrulf”)*(H:H=6))) would result in a #VALUE! error due to text in the column headings

but

=SUMPRODUCT((D:D),((G:G=”Endrulf”)*(H:H=6))) will give you the correct result (being 44,463,091).

You need to have at least two arguments in the second array or the formula will return 0. If you only have 1, you can get around it by inserting 1* e.g.:

I am using the 2003 Excel and I am trying to use two criteria, and to add the numbers in a third column. Now I have use this method in another workbook and it worked. But with the other workbook, the criteria’s were looking for “X” in both and the adding the third column when it applied. With this new workbook, the criteria’s are both numbers, and then add from a third column. The formula wont work if both the criteria are numbers for some reason. Do you have any suggestions?

Now, I want to know each type of flower sold in a particular date on another sheet. Please advise how can I do that. I believe this formulae does not work on Dates. I am required to play around. Please help.

Resources

Affiliate Program

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

✕

Hang On, Don't Go Just Yet.

As a thank you for visiting how would you like a10% Discount Code to use with any of my courses?