# 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".

The formula would read:

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

### Comments

**10 Responses to “SUMPRODUCTIF”**

**Leave a Reply**

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?

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.

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:

=HYPERLINK(RC[-1],”link”)

Brill, cheers.

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.

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!

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.

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.

This worked wonderfully and saved me many headaches! Thank you

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!

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:

=(SUMPRODUCT(N($Q$4:$Q$10>0),$I$4:$I$10,$AD$4:$AD$10,$V$4:$V$10) +SUMPRODUCT(N($Q$4:$Q$10=0),$E$4:$E$10,$U$4:$U$10,$V$4:$V$10))/ (SUMPRODUCT(N($Q$4:$Q$10>0),$I$4:$I$10,$V$4:$V$10)+ SUMPRODUCT(N($Q$4:$Q$10=0),$E$4:$E$10,$V$4:$V$10))