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
 
Ad

Advertisements

D

Domenic

Assumptions:

B1:D1 contains A, B, and C

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

B2:D7 contains your data


Formula:

=PRODUCT(IF(A2:A7="z",INDEX(B2:D7,0,MATCH("B",B1:D1,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(B2:D7,0,MATCH(H1,B1:D1,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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top