SUMPRODUCTIF

As mentioned a few weeks ago, I recently found out how to do the equivalent of a SUMPRODUCTIF. Here’s the detail on how.

First of all, some context.

SUM does exactly what it says on the tin. It sums a range of cells.

SUMPRODUCT is a lesser-known function, and sums a set of products. (a1*b1*c1) +(a2*b2*c2) + […] + (an*bn*cn)

SUMIF sums a range of cells if a certain condition is true. For example, sum people’s salaries in a column if they are based in London.

I wanted a way of putting a condition on the SUMPRODUCT function. That is, I wanted to do a sum of the products only where a condition for those rows held true.

To make the formulae more manageable, let’s assume I’ve named a few ranges: conditions is the column of data that I want to validate the condition against; range1 and range2 are the two ranges that I want to do the conditional SUMPRODUCT on. And let’s assume I only want to do this if the values in the conditions range are "London".

=SUMPRODUCT(–(conditions="London"),range1,range2)

Basically, the first term acts as a range in its own right, taking the value of 1 (for London) and 0 (for anything else). This means that it’s not actually doing a conditional SUMPRODUCT, but instead it’s multiplying the entries for which the condition fails by zero, which has the same effect. I think the double minus at the beginning is to ensure that the first argument is read as a formula.

Quite beautiful.

10 Responses to “SUMPRODUCTIF”

1. Ed on September 27th, 2006 10:44

Do you of a way of making completely relative references?

eg =(contents of cell below me)

Which is similar to asking: is there a formula for selfRow and selfCol?

2. Dan on September 27th, 2006 18:49

Ed,

I’m not sure why it wouldn’t work to just put “=A2” in cell A1. If you want the number of rows and columns across and down that you’re going to be dynamic, then you can switch to R1C1 notation (Tools | Options | General). That way you can refer to a cell “m” rows below and “n” columns across from where you are. I find that a confusing world, though…

Dan.

3. Ed, on September 29th, 2006 00:51

The reason I need relative cell refs is that I’m pasting in data rows from an interactive python session.

In Excel I have: name | url | link to url | more data etc
the link currently uses the formula: =HYPERLINK(B1,”link”) but I have to set this manually as different rows are constantly being used.

But using R1C1 notation as you suggest will do the job:

Brill, cheers.

4. Jeb on September 29th, 2006 01:02

Your double minus is telling Excel to convert the value “True” or “False” to 1 or 0. This is what the N() function is there for.

ie
=SUMPRODUCT(N(conditions=”London”),range1,range2)

Save the Earth. Use one less character.

5. Dan on September 29th, 2006 02:26

Thanks, Jeb – really useful. I didn’t know that it actually came back with text (True/False). I thought it was a visual thing with the number itself being returned.

I’ve learnt something new today; time to go home!

6. Jeb on September 30th, 2006 04:22

Actually I shouldn’t have written “”True” or “False”” as Excel actually has a type called logical value ie TRUE or FALSE, which is what (X=Y) will return.

Since Excel is weakly typed it will always try to convert to the type it thinks you need. You can actually have =LEFT(FALSE(),3) which will = “FAL”. Putting “–” before a logical value will convert it to a number since “-” only makes sense with a number.

I had to use the TYPE fuction to investigate as its hard to pin down otherwise.

7. Doug on October 17th, 2006 02:04

Guys:
Thank you for some ideas.
I couldn’t get it to work using any of the formulas above, but it gave me some ideas.
I used a formula such as:

=SUMPRODUCT(N(067:071=”London”, Q67:Q71)
this worked real well.
Thanks again.

8. Brandon on August 6th, 2008 14:16

This worked wonderfully and saved me many headaches! Thank you

9. Silvery on November 23rd, 2008 23:26

I used this in this way:

=SUM(SUMPRODUCT(N(\$E\$14:\$E\$111=\$G\$6),\$G\$14:\$G\$111,H\$14:H\$111)/DaysInMonth)
Column E = value being checked by cell G6
Columns G and H being multiplied and summed.
Worked great!

10. neil on March 13th, 2009 14:33

Thanks I used this to get a sumproduct with 2 conditions ie use one set of data under condition 1 and another set under condition 2 as follows: