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