Like Sumproduct, But Different
 

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.

Thanks,
Eric


--
ericsh
------------------------------------------------------------------------
ericsh's Profile: http://www.excelforum.com/member.php...o&userid=26149
View this thread: http://www.excelforum.com/showthread...hreadid=394775

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:D 1,0))))

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

Hope this helps!

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.

Thanks,
Eric


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=394775

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


"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.

Thanks,
Eric


--
ericsh
------------------------------------------------------------------------
ericsh's Profile: http://www.excelforum.com/member.php...o&userid=26149
View this thread: http://www.excelforum.com/showthread...hreadid=394775

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

Hi Michael,

If you always want Column C (labelled B) evaluated, then yes. But if
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 contains
the second criterion, such as 'B', then you can select the column you
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.
:)

Michael Wrote:
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


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=394775

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

Regards
Michael Mitchelson


"Domenic" wrote:


Hi Michael,

If you always want Column C (labelled B) evaluated, then yes. But if
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 contains
the second criterion, such as 'B', then you can select the column you
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.
:)

Michael Wrote:
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


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=394775

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


--
ericsh
------------------------------------------------------------------------
ericsh's Profile: http://www.excelforum.com/member.php...o&userid=26149
View this thread: http://www.excelforum.com/showthread...hreadid=394775