Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I need a product key for my Trail product, 2007 Microsoft Office s | Setting up and Configuration of Excel | |||
sum product sum if conditional HELP!!! | Excel Discussion (Misc queries) | |||
Conditional PRODUCT or SUM | Excel Worksheet Functions | |||
Custom function for Sum Product (Conditional Formula) | Excel Discussion (Misc queries) | |||
Conditional Sum Product | Excel Discussion (Misc queries) |