I need to write a PRODUCT array =PRODUCT(1+(the values in column t that have
a value in column d that match the value in D841)-1)

Is there a way to do a PRODUCT like a SUMIF?
ex: =SUMIF(D:D,D841,M:M)

It's to replace this formula:
=((1+T654/100)*(1+T671/100)*(1+T688/100)*(1+T705/100)*(1+T722/100)*(1+T739/100)*(1+T756/100)*(1+T773/100)*(1+T790/100)*(1+T807/100)*(1+T824/100)*(1+T841/100)-1)

I think this array-entered** formula will do the same thing as the long
formula that you posted...

=PRODUCT((1+(T654:T841*(MOD(ROW(T654:T841)-8,17)=0))/100))

**Commit formula using Ctrl+Shift+Enter, not just Enter by itself

That "-8" is needed to offset the row number to an even multiple of 17 so
that the MOD function can work properly. This particular formula also works
because you are adding "1" to the division (when the MOD function does not
evaluates to zero, the expression is FALSE which makes the entire division
zero and the "1" is left over to be multiplied by the accumulated product),
if you were to add a different number then the expression would need to be
modified).

--
Rick (MVP - Excel)



"eileenfz" wrote in message
...
I need to write a PRODUCT array =PRODUCT(1+(the values in column t that
have
a value in column d that match the value in D841)-1)

Is there a way to do a PRODUCT like a SUMIF?
ex: =SUMIF(D:D,D841,M:M)

It's to replace this formula:
=((1+T654/100)*(1+T671/100)*(1+T688/100)*(1+T705/100)*(1+T722/100)*(1+T739/100)*(1+T756/100)*(1+T773/100)*(1+T790/100)*(1+T807/100)*(1+T824/100)*(1+T841/100)-1)

Try

=PRODUCT((1+IF(D100:D841=$D$841,T100:T841,0)/100))-1

Adjust the cell ranges as necessary.

This is an array formula which must be entered by pressing CTRL+Shift+Enter
and not just Enter. If you do it correctly then Excel will put curly brackets
around the formula {}. You can't type these yourself. If you edit the formula
you must enter it again with CTRL+Shift+Enter.

Hope this helps,

Hutch

"eileenfz" wrote:

I need to write a PRODUCT array =PRODUCT(1+(the values in column t that have
a value in column d that match the value in D841)-1)

Is there a way to do a PRODUCT like a SUMIF?
ex: =SUMIF(D:D,D841,M:M)

It's to replace this formula:
=((1+T654/100)*(1+T671/100)*(1+T688/100)*(1+T705/100)*(1+T722/100)*(1+T739/100)*(1+T756/100)*(1+T773/100)*(1+T790/100)*(1+T807/100)*(1+T824/100)*(1+T841/100)-1)

If you are using Excel 2007, you can use whole columns:

=PRODUCT((1+IF(D:D=$D$841,T:T,0)/100))-1

Again, this must be entered using CTRL+Shift+Enter. I'm not sure if whole
columns will work with PRODUCT in earlier versions of Excel.

Hutch

"Tom Hutchins" wrote:

Try

=PRODUCT((1+IF(D100:D841=$D$841,T100:T841,0)/100))-1

Adjust the cell ranges as necessary.

This is an array formula which must be entered by pressing CTRL+Shift+Enter
and not just Enter. If you do it correctly then Excel will put curly brackets
around the formula {}. You can't type these yourself. If you edit the formula
you must enter it again with CTRL+Shift+Enter.

Hope this helps,

Hutch

"eileenfz" wrote:

I need to write a PRODUCT array =PRODUCT(1+(the values in column t that have
a value in column d that match the value in D841)-1)

Is there a way to do a PRODUCT like a SUMIF?
ex: =SUMIF(D:D,D841,M:M)

It's to replace this formula:
=((1+T654/100)*(1+T671/100)*(1+T688/100)*(1+T705/100)*(1+T722/100)*(1+T739/100)*(1+T756/100)*(1+T773/100)*(1+T790/100)*(1+T807/100)*(1+T824/100)*(1+T841/100)-1)