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:

1st	2nd	Val	Tax
a	P	2	0,2		1st criteria	b	4,9
b	x	3	0,1		2nd criteria	p	5
b	p	3	0,7
b	p	5	0,5		2nd in 1st	expected ->	4,6
c	o	5	0,6

<tbody>
</tbody>

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