Conditional Product
 

Hi
XL-XP. I'm trying to create a cell formula (not UDF) that gives me the
product of all lines that are true, e.g.

A, True, 2
B, True, 3
C, False,4
D, True, 5

Would give 30 (2*3*5)
I can get the sum of these without a problem with the array formula
{=SUMPRODUCT((B1:B4=TRUE)*C1:C4)}
but cannot find a corresponding PRODUCT-PRODUCT formula.

Any help gratefully appreciated.
John

Conditional Product
 

=PRODUCT(IF(B1:B5=TRUE,C1:C5))
Entered with Ctrl+Shift+enter rather than just Enter since this is an array
formula.

--
Regards,
Tom Ogilvy

"John" wrote in message
.. .
Hi
XL-XP. I'm trying to create a cell formula (not UDF) that gives me the
product of all lines that are true, e.g.

A, True, 2
B, True, 3
C, False,4
D, True, 5

Would give 30 (2*3*5)
I can get the sum of these without a problem with the array formula
{=SUMPRODUCT((B1:B4=TRUE)*C1:C4)}
but cannot find a corresponding PRODUCT-PRODUCT formula.

Any help gratefully appreciated.
John

Conditional Product
 

Hi
you don't have to array enter your formula. SUMPRODUCT would work
without it.

--
Regards
Frank Kabel
Frankfurt, Germany

"John" schrieb im Newsbeitrag
.. .
Hi
XL-XP. I'm trying to create a cell formula (not UDF) that gives me
the
product of all lines that are true, e.g.

A, True, 2
B, True, 3
C, False,4
D, True, 5

Would give 30 (2*3*5)
I can get the sum of these without a problem with the array formula
{=SUMPRODUCT((B1:B4=TRUE)*C1:C4)}
but cannot find a corresponding PRODUCT-PRODUCT formula.

Any help gratefully appreciated.
John

Conditional Product
 

Tom ....Many thanks, you're a star - as always :-)

"Tom Ogilvy" wrote in message
...
=PRODUCT(IF(B1:B5=TRUE,C1:C5))
Entered with Ctrl+Shift+enter rather than just Enter since this is an
array
formula.

--
Regards,
Tom Ogilvy

"John" wrote in message
.. .
Hi
XL-XP. I'm trying to create a cell formula (not UDF) that gives me the
product of all lines that are true, e.g.

A, True, 2
B, True, 3
C, False,4
D, True, 5

Would give 30 (2*3*5)
I can get the sum of these without a problem with the array formula
{=SUMPRODUCT((B1:B4=TRUE)*C1:C4)}
but cannot find a corresponding PRODUCT-PRODUCT formula.

Any help gratefully appreciated.
John