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”

  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:
    =HYPERLINK(RC[-1],”link”)

    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:

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

Leave a Reply