Multiplying columns if criteria is met

[oscarj]

Hello,

Probably simple question here, but I am struggling with SUMIFS and SUMPRODUCT so I am reaching out for help. I have 3 columns: Two have values that need to be multiplied if a condition in the 3rd column is met. For simplicity, here is an example

[TABLE="width: 500"]
<tbody>[TR]
[TD]Value 1[/TD]
[TD]Value 2[/TD]
[TD]Criteria[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]10[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]20[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]30[/TD]
[TD]Yes[/TD]
[/TR]
</tbody>[/TABLE]

The result would be 2*10 + 4*30 = 140. The actual sheet will have 2000 rows though. What is the easiest way to do this?

Thank you

Oscar

 

[oscarj]

Clarification: Would prefer to do it all in one formula without using helper columns

 

[AlphaFrog]

Try this...

=SUMPRODUCT((A2:A4)*(B2:B4)*(C2:C4="Yes"))

 

[oscarj]

Thank you!

 

[jtakw]

Hi,

SUMPRODUCT:

Book1
	A	B	C	D	E
1	Value 1	Value 2	Criteria		140
2	2	10	Yes
3	3	20	No
4	4	30	Yes
Sheet104

Cell Formulas
Range	Formula
E1	=SUMPRODUCT((A2:A4)*(B2:B4)*(C2:C4="Yes"))

Adjust Cell references to suit your data.

 

[oscarj]

Is there a way to do this in the case where instead of yes or no, criteria is blank, and a blank is assumed to be a no?

 

[jtakw]

Like this:

Book1
	A	B	C	D	E	F
1	Value 1	Value 2	Criteria		440	260
2	2	10	Yes
3	3	20		C3 is ""
4	4	30	Yes
5	5	40		C5 is True Blank
6	6	50	Yes
Sheet104

Cell Formulas
Range	Formula
E1	=SUMPRODUCT((A2:A6)*(B2:B6)*(C2:C6="Yes"))
F1	=SUMPRODUCT((A2:A6)*(B2:B6)*(C2:C6=""))
C3	=""

 

[AlphaFrog]

Is there a way to do this in the case where instead of yes or no, criteria is blank, and a blank is assumed to be a no?

Do you mean that the data in column C is either not blank or blank. And you want to multiply rows that are not blank in column C?

If yes,...

=SUMPRODUCT((A2:A4)*(B2:B4)*(C2:C4<>""))

 

[oscarj]

I should've said the three options are "yes" "no" or a blank cell. Although not actually blank, but with "" in the cell. So if there is a yes, they should be multiplied and summed, but if there is a no or "" nothing should be done. Is there a way to do that?

 

[oscarj]

I decided to stick with the original calculation and change the contents of the cells i'm multiplying from IFERROR(___,"") to IFERROR(___,0) so the sumproduct works.

Thank you both for the help!