Combine sumproduct with IF

[alyssa75]

Formula currently is something like:

Sumproduct(b1:b100,c1:c100) to get the aggregate of (b1*c1)+(b2+c2)+.....etc


How can I add a condition to only perform include in formula if D = something.

So needing a simplified version of the following longhand formula:

if(D1 = a35,b1*c1,0)+if(D2=a35,b2*c2,0)+...etc...all the way through the end of the range. say row 100.

Any ideas?

Thanks!

 

[bosco_yip]

=SUMPRODUCT(--(D1:D100=A35),B1:B100,C1:C100)

Regards

 

[taigovinda]

Your formula:

Sumproduct(b1:b100,c1:c100)

would become this:

Sumproduct(b1:b100,c1:c100,--(d1:d100=$a$35))

...see here for a great tutorial on how to use sumproduct(): http://www.xldynamic.com/source/xld.SUMPRODUCT.html

There seems to be something wrong with the site right now, but it seems to work fine as long as you don't move your mouse...

 

[jrudner]

Hi, how would the formula if there were multiple if-conditions that had to be met? something that in a perfect world would be called SUMPRODUCTIFS?

Thanks,

JRudner

 

[barry houdini]

Do you mean multiple conditions in separate columns? You can just add conditions, e.g.

to count rows where column A = "x" and column B = "y"

=SUMPRODUCT(--(A$2:A$100="x"),--(B$2:B$100="y"))

or to sum column C when the same 2 conditions are met

=SUMPRODUCT(--(A$2:A$100="x"),--(B$2:B$100="y"),C$2:C100)

....add more conditions by adding more instances like --(A$2:A$100="x") separated by commas

 

[PlusBob]

Yes, I believe... This is what I have now:
=SUMPRODUCT(('Timecard Detail FY16'!E2:E7601,M3)*(--(MONTH('Timecard Detail FY16'!H2:H7601)=10))*('Timecard Detail FY16'!M2:M7601))

But I a get a #VALUE! error.

 

[dUBBINS]

.

 

[dUBBINS]

I am not an expert like many in this forum, but the "M3" might be the problem. I do not see how that fits into the syntax of SUMPRODUCT.

 

[Jonmo1]

Probably

=SUMPRODUCT(--('Timecard Detail FY16'!E2:E7601=M3),--(MONTH('Timecard Detail FY16'!H2:H7601)=10),'Timecard Detail FY16'!M2:M7601)

 

[PlusBob]

M3 is the reference cell (IRL) the cell reference the list of names. So for example M3=John; M4;Bob, etc.