# Like Sumproduct, But Different

E

#### 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

D

#### Domenic

Assumptions:

B11 contains A, B, and C

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

Formula:

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

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

Hope this helps!

G

#### 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

H

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

D

#### 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

G

#### Guest

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

Regards
Michael Mitchelson

E

#### ericsh

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