Like Sumproduct, But Different

[ericsh]

I've used the sumproduct function to add values the meet multipl
conditions. I'm looking for a similar function that would multiply th
values. For instance, given the following data:

A B C
x 1 7 3
y 2 8 4
z 3 9 5
x 4 0 6
y 5 1 7
z 6 2 8


sumproduct((ABC="B")*(xyz="z"),values) = 11

I want something that will find the values that meet my conditions an
then multiplies them. In this case, Bz#1=9 and Bz#2=2, so the resul
would be 18.

Thanks,
Eri

 

[Domenic]

Assumptions:

B11 contains A, B, and C

A2:A7 contains x, y, z, x, y, and z

B27 contains your data


Formula:

=PRODUCT(IF(A2:A7="z",INDEX(B27,0,MATCH("B",B11,0))))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

 

[Guest]

Hi Domenic
Wouldn't this do the same thing.

=PRODUCT(IF(A4:A9="Z",C4:C9,""))

If I don't ask, I don't learn

Regards
Michael Mitchelson

 

[Harlan Grove]

ericsh wrote...

I've used the sumproduct function to add values the meet multiple
conditions. I'm looking for a similar function that would multiply the
values. For instance, given the following data:

A B C
x 1 7 3
y 2 8 4
z 3 9 5
x 4 0 6
y 5 1 7
z 6 2 8

sumproduct((ABC="B")*(xyz="z"),values) = 11

I want something that will find the values that meet my conditions and
then multiplies them. In this case, Bz#1=9 and Bz#2=2, so the result
would be 18.

....

You have zeros in your values, so the following won't work. But it you
had only positive numbers, you could have used

=EXP(SUMPRODUCT((ABC="B")*(xyz="z"),LN(values)))

 

[Domenic]

Hi Michael,

If you always want Column C (labelled B) evaluated, then yes. But i
you change my formula as follows...

=PRODUCT(IF(A2:A7=G1,INDEX(B27,0,MATCH(H1,B11,0))))

...where G1 contains the first crieterion, such as 'x', and H1 contain
the second criterion, such as 'B', then you can select the column yo
want evaluated by entering the appropriate label in H1, such as A, B
or C. If I'm not mistaken, I think that's what the OP was looking for

 

[Guest]

Thanks Domenic
I see the point.
Another one for the Michael archives

Regards
Michael Mitchelson

 

[ericsh]

Thanks for your help guys, the Product function used as an array did the
trick.