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

Excel SUMPRODUCT Example

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

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

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:

Excel SUMPRODUCT Applied to an Example

= 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

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

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

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

As an Alternative to Helper Columns

Excel SUMPRODUCT Example

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!

Want More Excel Formulas

Why not visit our list of Excel formulas. You'll find a huge range all explained in plain English, plus PivotTables and other Excel tools and tricks. Enjoy :)

Share This

Please share this or leave a comment and I'll make sure you get a personal reply.

Leave a Comment

Current day month ye@r *

{ 96 comments… read them below or add one }

Ash Gupta December 31, 2011 at 2:09 am

Good website

Reply

Mynda Treacy December 31, 2011 at 8:29 pm

Thanks Ash.

Reply

William Hayling June 15, 2012 at 4:14 am

Thanks you made it easy to understand

Reply

Mynda Treacy June 15, 2012 at 5:31 pm

Cheers, William L:)

Reply

Vidak Milatovic June 16, 2012 at 7:05 pm

Nice! Thanks for explaining it clearly
These things should be in excel help, they are very useful

Reply

Mynda Treacy June 16, 2012 at 8:22 pm

Thanks, Vidak :)

Reply

Dan June 26, 2012 at 8:55 pm

Very helpful – I needed the datevalue part of the function.

thanks

Reply

Mynda Treacy June 27, 2012 at 8:42 am

Thanks, Dan. Glad to have helped :)

Reply

Robert July 7, 2012 at 1:54 pm

Now I know what I have using without understanding it.

Reply

Mynda Treacy July 7, 2012 at 6:55 pm

:) Glad it was helpful.

Reply

ATUL SRIVASTAVA July 9, 2012 at 8:44 pm

Hi Mynda,

I wonder if it works when one of the criteria is Date (MM/DD/YYYY). I have been trying it, but could not succeed. For example,

Date Flower Number
2/1/2012 JASMINE 10
2/1/2012 ROSE 15
2/1/2012 LAVENDER 20
2/1/2012 LAVENDER 5
2/1/2012 ROSE 9
2/1/2012 JASMINE 15
2/2/2012 JASMINE 18
2/2/2012 JASMINE 12
2/2/2012 LAVENDER 22
2/2/2012 ROSE 55
2/2/2012 LAVENDER 12
2/2/2012 ROSE 69
2/2/2012 ROSE 80

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.

Reply

Mynda Treacy July 10, 2012 at 9:44 pm

Hi Atul,

It does work on dates, as you can see in my example above, but you need to wrap them in a DATEVALUE function, or use the date serial number, or reference a cell containing the date.

The format of the date i.e. dd/mm/yy or mm/dd/yy shouldn’t matter as Excel will automatically interpret it based on your Excel and system settings.

I suggest you download the workbook for the tutorial above and play around with the formula that uses dates to see if you can find where you’re going wrong.

Kind regards,

Mynda.

Reply

Julie July 27, 2012 at 2:03 am

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?

Reply

Mynda Treacy July 27, 2012 at 11:19 am

Hi Julie,

I’d need to see your formula to know what the problem might be.

Kind regards,

Mynda.

Reply

Julie July 27, 2012 at 10:02 pm

=SUMPRODUCT((ColI=”X”)*(TVCC=”X”)*Total) This is the formula that worked, I want the exact same thing but replace the “X”‘s with 5 to 9 digit numbers.

Thanks

Reply

Mynda Treacy July 29, 2012 at 11:44 am

Hi Julie,

If you replace the x’s with numbers then you don’t put them inside double quotes….unless of course they are text and not numbers. In which case you would put them inside double quotes.

Remember double quotes tell Excel the data is text not a value/number.

If you can’t get it to work you’ll need to send me the workbook so I can see the data you’re working with.

Kind regards,

Mynda.

Reply

Julie July 30, 2012 at 11:57 pm

That was it! Thank you very much Mynda, that was very helpful. I forgot about that rule with the quotes.

I really appreciate the quick responses aswell.

Best wishes,

Julie.

Vaidehi August 9, 2012 at 12:20 pm

Very useful & easy to absorbe. cheers,

Reply

Mynda Treacy August 9, 2012 at 9:12 pm

Cheers, Vaidehi :)

Reply

Kenneth Flickstein September 27, 2012 at 8:02 am

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

This will over count if both conditions of your “or” are true. It should be

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

Reply

Mynda Treacy September 27, 2012 at 3:25 pm

Hi Kenneth,

Thanks for your comment, but since Rens and Endrulf are in the same column (solarSystem) they can’t both be true at the same time therefore double counting in this instance isn’t a concern.

Also note; your formula evaluates to 654,429,777 which is the SUM of volume where Jumps = 6. It is ignoring the OR statement in the SUMPRODUCT.

Kind regards,

Mynda.

Reply

Elton October 23, 2012 at 8:13 am

I don’t know if it’s just me, but I tried what you suggested and had an error but found out what was wrong. Instead of

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

it should be

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

Use a comma instead of asterisk after the array you want to sum. Just FYI.

Reply

Mynda Treacy October 23, 2012 at 1:14 pm

Hi Elton,

Thanks for your comment. Both methods give the same result for me.

Kind regards,

Mynda.

Reply

Alison January 10, 2013 at 9:16 am

Hi,

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.:

=SUMPRODUCT((D:D),1*(G:G=”Endrulf”))

however this is a bit of a kludge.

Anyways… that’s my two cents!

Reply

Mynda Treacy January 10, 2013 at 8:55 pm

Cheers, Alison :)

Reply

Nuzry October 28, 2012 at 4:05 am

i need to ask a question on this…
i have an excel sheet which contains my problem…how can i attach it…

Reply

Mynda Treacy October 28, 2012 at 4:47 pm

Hi Nurzy,

You can send me files by logging a ticket on the help desk.

Kind regards,

Mynda.

Reply

Marlo Kyn Bunda October 30, 2012 at 6:23 am

Thanks for this. Now I understand well the power of SUMPRODUCT…

Reply

Mynda Treacy October 30, 2012 at 7:45 am

You’re welcome, Marlo :)

Reply

Minku Bhatia November 5, 2012 at 2:59 am

Hi Mynda,

Thanks Mynda for such a clear explanation of function.

Reply

Mynda Treacy November 5, 2012 at 2:06 pm

:) You’re welcome.

Reply

joseph November 12, 2012 at 5:45 pm

kudos,excellent work

Reply

Mynda Treacy November 12, 2012 at 10:33 pm

Thank you, Joseph :)

Reply

Vicky Singh November 23, 2012 at 5:59 am

Hi,
Thanks for your detailed explanation however could you please send some more exercise to practice on

Thanks in advance.

Regards,
Vicky

Reply

Mynda Treacy November 23, 2012 at 8:10 am

Hi Vicky,

If you want to join my Excel course you receive Excel workbooks with homework questions for practice.

Kind regards,

Mynda.

Reply

Santhanaganesan December 18, 2012 at 1:42 am

Thank u, I was searching for this solution

Reply

Otto Nielsen January 23, 2013 at 6:59 am

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

Reply

Mynda Treacy January 23, 2013 at 11:05 am

:) cheers, Otto.

Reply

Peter Day January 31, 2013 at 5:33 pm

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?

Reply

Mynda Treacy January 31, 2013 at 8:39 pm

Hi Peter,

I’d choose SUMPRODUCT, but if you feel more comfortable with the SUMIFS then go with that.

Kind regards,

Mynda.

Reply

Justin February 2, 2013 at 4:49 am

Hi

I would like to calculate the sum of all values in a range which are NOT equal to the criteria of two values (each of which are in different ranges).

So the ranges are as below:

R1 R2
1 2
1 3
1 4
2 2
2 3
1 2
2 4
2 6
2 4

So, if the value in R1 is 1 and the value in R2 is 2 calculate the sum of the values remaining in R1.

So there are 2 rows where R1 is 1 and R2 is 2.
Adding up the remaining values in R1 gives a total of 12.
How do I calculate the answer 12?

Another example from the ranges above is
Calculate the sum of all values remaining in R1 after the following is met:
R1=1 and R2=2
AND
R1=2 and R2=4

Answer is 8

Many thanks for your help!!

Reply

Carlo Estopia February 2, 2013 at 8:51 pm

Dear Justin,

Quite a brain twister you’ve got there. I don’t know why SumIF or SUmproduct alone won’t work using “<>“(not equal) conditions.
Instead of an “AND” effect, it is more like getting an “OR”

so I improvised: Note : Row1 and Row 2… columns A to I.

 =SUM(A1:I1)-SUMIFS(A1:I1,A1:I1,"=1",A2:I2,"=2") 

this will result to 12.

 =SUM(A1:I1)-SUMPRODUCT((A1:I1)*(((A2:I2=2)*(A1:I1=1))+((A2:I2=4)*(A1:I1=2))))

this will result to 8

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.

Read more on SUMIFS and SUMPRODUCTS

Sincerely,

Carlo

Reply

Bob Phillips April 8, 2013 at 9:18 pm

Or you could use

=SUMPRODUCT(SIGN((A2:A10R1)+(B2:B10R2)),A2:A10)

Reply

Sorin February 8, 2013 at 9:39 pm

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.

Best regards,
Sorin.

Reply

Carlo Estopia February 9, 2013 at 7:41 pm

Hi Sorin,

I never thought you were asking a question. Sorry. ;)

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.

Cheers.

CarloE

Reply

Bob Phillips April 8, 2013 at 9:32 pm

Combine the LEFT function to get what you want

=SUMPRODUCT(–(LEFT(solarSystem,1)=”E”),–(jumps=6),volume)

Reply

Sorin February 10, 2013 at 5:17 am

Hi, Carlo.

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…}.

Thank you very mutch.
Sorry Mynda for ‘i’.

Best regards,
Sorin.

Reply

Mynda Treacy February 10, 2013 at 8:55 pm

Hi Sorin,

Glad you found a solution. Well done :)

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

More on array formulas here.

I hope that helps.

Kind regards,

Mynda.

Reply

Carlo Estopia February 10, 2013 at 10:57 pm

Hi Sorin,

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.

Sincerely,

CarloE

Reply

Sorin February 21, 2013 at 3:54 am

Hi Mynda,
Thank you very much for explanations. This explain a lot. :-)

Yes Carlo, it is very simple your solution, but some times we don’t find a easiest solutions.
I post the solution I was find, not best solution. Thank you for support.

Best regards,
Sorin

Reply

Carlo Estopia February 21, 2013 at 3:20 pm

Hi Sorin,

On behalf of Mynda and Philip, you’re welcome.

Cheers.

Carlo

Reply

Gaurav February 16, 2013 at 11:03 pm

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 ?

Reply

Carlo Estopia February 18, 2013 at 11:07 am

Hi Gaurav,

Greetings.

Yes you can… very much.

Try this example.
Assume the data

    A       B     C
1 Names	 Value1 Value2
2 Name1	   10	  10
3 Name2	   20	  20
4 Name3	   30	  30

paste this formula anywhere in the sheet.

=SUMPRODUCT(((B2:B4)*(C2:C4))*(A2:A4="Name1"))

Please read more on SUMPRODUCT.

Cheers.

CarloE

Reply

Gaurav February 18, 2013 at 9:35 pm

Carloe…

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 !

Regards
Gaurav Sahni, India

Reply

Carlo Estopia February 19, 2013 at 12:27 am

Hi Gaurav,

On behalf of Mynda and Philip, I say You’re Welcome!!!

It always feel good to have someone appreciate ones work too.
So thank you too.

Cheers.

CarloE

Reply

Bob Phillips April 8, 2013 at 9:38 pm

You don’t need to do the multiply in the formula, SUMPRODUCT does the multiply (PRODUCT), so you can use

=SUMPRODUCT(–(A2:A4=”Name1″),B2:B4,C2:C4)

which will also handle text values in the sum ranges as mentioned elsewhere.

Reply

Lisa February 20, 2013 at 9:55 pm

Hi Mynda,

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.

My formula doesn’t seem to be adding up properly though. I have the following:
Date Budget Amount
01/01/13 Stationery 12.00
02/01/13 Expenses 5.00
07/01/13 Entertainement 7.00

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?

Reply

Carlo Estopia February 21, 2013 at 3:47 pm

Hi Lisa,

Please use this formula.

=SUMPRODUCT((Amount)*((Budget="Expenses")*(AND(Date>=DATEVALUE("01/01/2013"),Date< =DATEVALUE("07/01/13")))))

Assumptions: Named Ranges: Budget, Date and Amount

  A                B               C
1 Date	        Budget   	Amount
2 1/1/2013	Stationery	12
3 2/1/2013	Expenses	5
4 7/1/2013	Entertainment	7

Read More: Sumproduct

Cheers.

CarloE

Reply

Lisa March 25, 2013 at 2:04 am

Ooops sorry something went wrong in my comment.

I meant to say I used the formula =SUMPRODUCT((amount)*((budget=”Expenses”)*(AND(date>=DATEVALUE(“01/01/13″); date=DATEVALUE(“01/01/2011″)*(Date<=DATEVALUE(“31/01/2011″))))?

Thanks again;

-Lisa

Reply

Carlo Estopia March 25, 2013 at 8:50 pm

Hi Lisa,

I don’t know if it is already working or not, judging on how you wrote your feedback. :)

Anyway, Just send your concern via HELP DESK.

Cheers.

CarloE

Reply

Lisa March 28, 2013 at 9:53 pm

Thanks Carlo, I send the details to the Helpdesk. :)

Bob Phillips April 8, 2013 at 9:45 pm

ARe you sure that formula works. I don’t think it does, because the AND will return a single TRUE/FALSE result, not an array of TRUE/FALSE that evaluates those date conditions.

This works perfectly fine

=SUMPRODUCT(–(Budget=”Expenses”),–(Date>=–(“2013-01-01″)),–(Date<=–("2013-07-01")),Amount)

I would also advise using this ISO standard date format to remove any ambiguity as to what the date being tested actually is (is 01/07/2013 7th Jan or 1st July?).

Reply

Dusmanta das March 4, 2013 at 10:53 pm

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.

Reply

Carlo Estopia March 5, 2013 at 9:35 am

Hi Dusmanta,

I think all you need to do is to format your cells.
Right click the cells where your dates are and
1 Format Cells
2 This will bring you to the Number’s tab
3 Select Custom -> “mm/dd/yyyy”

Please read more on formatting cells.

Now you may not find an exact format :mm/dd/yyyy.
Just choose the closest or any custom date
format for that matter and manually edit the same
to mm/dd/yyyy.

Cheers.

CarloE

Reply

Chris March 26, 2013 at 6:32 am

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!

Reply

Carlo Estopia March 26, 2013 at 11:52 am

Hi Chris,

Try reading Array Formulas.

Or you might send your file to Help Desk.

Cheers.

CarloE

Reply

Bob Phillips April 8, 2013 at 9:46 pm

Or use dynamic named ranges.

Reply

Seth Proctor April 12, 2013 at 5:29 pm

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

=SUMPRODUCT((‘Referral Progress’!$D$1:$D$8197=’Area Overview’!$A4)*(‘Referral Progress’!$J$1:$J$8197>=’Area Overview’!K$1)*(‘Referral Progress’!$J$1:$J$8197<'Area Overview'!T$1))

Thank you in advance

Reply

Carlo Estopia April 12, 2013 at 5:38 pm

Hi Seth,

I would like to inform you that I don’t have 2003 anymore.

However, Let’s see what we can do. Please send that file via HELP DESK.

Cheers,

CarloE

Reply

gautam sanyal May 8, 2013 at 12:56 am

I have gone through your excel formula,and i found it is very useful tool .

Reply

Mynda Treacy May 8, 2013 at 10:00 am

Thanks, Gautam :)

Reply

TCC Sampit May 8, 2013 at 4:46 pm

truly a great lesson, I had a computer course and I also give lessons on excel, and this website provides the motivation for me. thank you for sharing. Greetings TCC Sampit.

Reply

Mynda Treacy May 8, 2013 at 7:55 pm

You’re welcome, TCC Sampit :)

Reply

Acpt May 13, 2013 at 9:55 pm

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)

Reply

Mynda Treacy May 15, 2013 at 8:00 pm

Hi Acpt,

Like this:

=SUMPRODUCT(LARGE((Volume)*(solarSystem="Endrulf")*(jumps=6),ROW(1:5)))

Kind regards,

Mynda.

Reply

Pradeep May 15, 2013 at 4:26 pm

Hi Mynda,

Sorry I am late in the party, Thanks for making Sumproduct formula easy to understand.. till now i hv understood that we use ‘+’ sign as an OR and ‘*’ sign as an AND operator in Sumproduct formula.

I have seen many people use ‘- -’ in a sumproduct, will appreciate if you can explain the use and why it is used please ?

Thanks in advance
Pradeep

Reply

Mynda Treacy May 15, 2013 at 7:44 pm

Hi Pradeep,

The double unary ‘- -’ is used to convert the boolean TRUE/FALSE to their numeric equivalents of 1 and 0.

Kind regards,

Mynda.

Reply

Paddy Dive May 20, 2013 at 8:03 pm

Thanks Mynda..
i got it.. but little unsure… a small example will help… may be in your next blog, or else u can write a next one to explain..

Thanks for all your help.
Pradeep

Reply

Mynda Treacy May 20, 2013 at 10:09 pm

Sure, maybe next time.

Reply

Sheena September 5, 2013 at 8:38 am

This really helped a lot. I had a query regarding the sumproduct function. Could I mail u the worksheet ?

Reply

Mynda Treacy September 5, 2013 at 11:35 am

Hi Sheena,

You can send worksheets and questions via the help desk.

Kind regards,

Mynda.

Reply

sheena September 5, 2013 at 5:42 pm

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:

Color1 weight1 Color2 Weight2 Color3 Weight3 Color4 weight4
white 280 white 48 indigo 56 red 23
red 34 indigo 25 Blue 65 red 32
Blue 23 red 51 Blue 89 indigo 51
Blue 272 orange 35 orange 40 Blue 27

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?

Reply

Mynda Treacy September 6, 2013 at 9:34 am

Hi Sheena,

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):

=SUMPRODUCT(({0,0,0,0;0,0,1,0;1,0,1,0;1,0,0,1}),(280,48,56,23;34,25,65,32;23,51,89,51;272,35,40,27))

SUMPRODUCT then multiplies the arrays:

=SUMPRODUCT(0*280,0*48,0*56,0*23;0,34,0*25,1*65,0*32;1*23,0*51,1*89,0*51;1*272,0*35,0*40,1*27)

And you get 476

I hope that helps.

Kind regards,

Mynda.

Reply

Pankaj September 26, 2013 at 10:35 pm

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.

The data looks something like this:
Name Currency Salary Band
EMP1 USD 6250
EMP2 USD 3300
EMP3 EURO 3673
EMP4 EURO 10167

There are four bands, e.g. for USD, they are
USD-A: 8000

How can I fill up the band using a formula

Thanks

Reply

Mynda Treacy September 26, 2013 at 10:51 pm

Hi Pankaj,

You need VLOOKUP with a sorted list for this.

Kind regards,

Mynda.

Reply

German Prieto September 28, 2013 at 9:01 am

Hi Mynda,

Thank you for this great trick!

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.

Thank you for your help

Reply

Mynda Treacy September 28, 2013 at 4:23 pm

Hi German,

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.

Instead you can use this formula to achieve what you want:

=SUM(A1:A3*IF((B1:B3<2)+(C1:C3<2),1))

Entered with CTRL+SHIFT+ENTER as it's an array formula

Kind regards,

Mynda.

Reply

haider October 24, 2013 at 10:26 am

Nice! Thanks for explaining it clearly
you are a great women

Reply

Mynda Treacy October 24, 2013 at 12:47 pm

Thank you, Haider :)

Reply

mano October 26, 2013 at 6:38 am

mynad dear
good website your .mr30 ,thanks

Reply

Mynda Treacy October 26, 2013 at 8:03 am

Thanks, Mano :)

Reply

Jawa Herath February 10, 2014 at 5:30 pm

Realy Good contents & very useful tips are available in this site.

Reply

Philip Treacy February 10, 2014 at 7:36 pm

Thanks Jawa

Reply

jraju April 10, 2014 at 4:50 pm

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

Reply

Catalin Bombea April 10, 2014 at 11:23 pm

Hi Jraju,
Please upload to Help Desk: http://www.myonlinetraininghub.com/help-desk a sample workbook with your data, it will be easier for us to understand your situation.
Catalin

Reply

Mynda Treacy July 31, 2012 at 7:55 am

You’re welcome, Julie :) Thanks for letting me know you sorted it out.

Reply

Previous post:

Next post: