SumProduct with two criterias

[RZoig]

Hello,

Marcelo helped me with a sumproduct formula that would only return results that matched a certain criteria.

this is the formula "=SUMPRODUCT(($C$2:$C$6)*($A$2:$A$6=$G2);($D$2:$D$6)*($A$2:$A$6=$G2))"
criteria is on G2 (b), criteria array on A2:A6. Result is 4,9.

But I want a 2nd criteria and the results should only SUMPRODUCT 2nd criteria nested within 1st criteria.
It must be a single formula, however complicated.

This is the array, placed in A1:

[TABLE="width: 543"]
<tbody>[TR]
[TD]1st[/TD]
[TD]2nd[/TD]
[TD]Val[/TD]
[TD]Tax[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]P[/TD]
[TD]2[/TD]
[TD]0,2[/TD]
[TD][/TD]
[TD]1st criteria[/TD]
[TD]b[/TD]
[TD="align: right"]4,9[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]x[/TD]
[TD]3[/TD]
[TD]0,1[/TD]
[TD][/TD]
[TD]2nd criteria[/TD]
[TD]p[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]p[/TD]
[TD]3[/TD]
[TD]0,7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]p[/TD]
[TD]5[/TD]
[TD]0,5[/TD]
[TD][/TD]
[TD]2nd in 1st[/TD]
[TD]expected ->[/TD]
[TD="align: right"]4,6[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD]o[/TD]
[TD]5[/TD]
[TD]0,6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

In other words, I want SUMPRODUCT Val and Tax with 2nd criteria = 'p' ONLY WHEN 1st criteria ='b'
Result must be 4,6

Thank you.

 

[Marcelo Branco]

Try

=SUMPRODUCT(--(A2:A6=G2),--(B2:B6=G3),C2:C6*D2:D6)

M.

 

[steve the fish]

Try:

=SUMPRODUCT(($A$2:$A$6=$G$2)*($B$2:$B$6=$G$3)*$C$2:$C$6*$D$2:$D$6)

 

[RZoig]

Thank you guys!

Both worked. I was trying to use AND but it seems I was complicating things.

But I gotta ask, Marcelo: Whata heck is that?? --(A2:A6=G2)? --?? What that stands for???

 

[Marcelo Branco]

SUMPRODUCT requires arrays of numbers
The comparison (A2:A6=G2) returns an array of logical values False or True like
{False;True;True;True;False}
-- converts False and True to, respectively, 0 and 1
{0;1;1;1;0}

M.

 

[RZoig]

Simple things with great results

That's what I like the best.

Thanks again!
( Valeu Marcelo! )