| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mulitple SUMPRODUCTS?
Hello,
Please review the formula I have created below. Is there a way to have mulitple criteria's? Is this example, I used =211. I like to use multiple #'s. I don't think that nesting is the answser. Please advise. Rich =SUMPRODUCT(--('K:\0506FISC\SSOP''s June 06\[3308 - Trial Balance.xls]3308 - Trial Balance 11-1-2006'!$C$2:$C$1500=A23),--('K:\0506FISC\SSOP''s June 06\[3308 - Trial Balance.xls]3308 - Trial Balance 11-1-2006'!$B$2:$B$1500=211),--('K:\0506FISC\SSOP''s June 06\[3308 - Trial Balance.xls]3308 - Trial Balance 11-1-2006'!$D$2:$D$1500)) |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Mulitple SUMPRODUCTS?
|
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Mulitple SUMPRODUCTS?
Thanks Don,
I tried it, however, it came back with a #REF error. Any suggestions? =SUMPRODUCT(--('K:\0506FISC\SSOP''s June 06\[3308 - Trial Balance.xls]3308 - Trial Balance 11-1-2006'!$C$2:$C$1500=A23),--('K:\0506FISC\SSOP''s June 06\[3308 - Trial Balance.xls]3308 - Trial Balance 11-1-2006'!$B$2:$B$1500={211,240,245,252,253}),--('K:\0506FISC\SSOP''s June 06\[3308 - Trial Balance.xls]3308 - Trial Balance 11-1-2006'!$D$2:$D$1500)) "Don Guillett" wrote: ={211,215,330} -- Don Guillett SalesAid Software "RJMAT" wrote in message ... Hello, Please review the formula I have created below. Is there a way to have mulitple criteria's? Is this example, I used =211. I like to use multiple #'s. I don't think that nesting is the answser. Please advise. Rich =SUMPRODUCT(--('K:\0506FISC\SSOP''s June 06\[3308 - Trial Balance.xls]3308 - Trial Balance 11-1-2006'!$C$2:$C$1500=A23),--('K:\0506FISC\SSOP''s June 06\[3308 - Trial Balance.xls]3308 - Trial Balance 11-1-2006'!$B$2:$B$1500=211),--('K:\0506FISC\SSOP''s June 06\[3308 - Trial Balance.xls]3308 - Trial Balance 11-1-2006'!$D$2:$D$1500)) |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Mulitple SUMPRODUCTS?
Correction, it gives a #value error.
SUMPRODUCT(--('[Normal Trial Balance.xls]3308 - Trial Balance 11-1-2006'!$C$2:$C$1500=A23),--('[Normal Trial Balance.xls]3308 - Trial Balance 11-1-2006'!$B$2:$B$1500={211,240,245,252,253}),--('[Normal Trial Balance.xls]3308 - Trial Balance 11-1-2006'!$D$2:$D$1500)) "RJMAT" wrote: Thanks Don, I tried it, however, it came back with a #REF error. Any suggestions? =SUMPRODUCT(--('K:\0506FISC\SSOP''s June 06\[3308 - Trial Balance.xls]3308 - Trial Balance 11-1-2006'!$C$2:$C$1500=A23),--('K:\0506FISC\SSOP''s June 06\[3308 - Trial Balance.xls]3308 - Trial Balance 11-1-2006'!$B$2:$B$1500={211,240,245,252,253}),--('K:\0506FISC\SSOP''s June 06\[3308 - Trial Balance.xls]3308 - Trial Balance 11-1-2006'!$D$2:$D$1500)) "Don Guillett" wrote: ={211,215,330} -- Don Guillett SalesAid Software "RJMAT" wrote in message ... Hello, Please review the formula I have created below. Is there a way to have mulitple criteria's? Is this example, I used =211. I like to use multiple #'s. I don't think that nesting is the answser. Please advise. Rich =SUMPRODUCT(--('K:\0506FISC\SSOP''s June 06\[3308 - Trial Balance.xls]3308 - Trial Balance 11-1-2006'!$C$2:$C$1500=A23),--('K:\0506FISC\SSOP''s June 06\[3308 - Trial Balance.xls]3308 - Trial Balance 11-1-2006'!$B$2:$B$1500=211),--('K:\0506FISC\SSOP''s June 06\[3308 - Trial Balance.xls]3308 - Trial Balance 11-1-2006'!$D$2:$D$1500)) |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Mulitple SUMPRODUCTS?
Hi Rich,
rather use *, not the comma between complex sumproduct terms. It is one of the many cases the favorable comma (10% faster) does not work. -- Moin+Gruss Alexander - MVP für MS Excel - www.xxcl.de - mso2000sp3 --7-2 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Mulitple SUMPRODUCTS?
Thank You Alexander,
This did the trick. I am very happy now. Have a nice day! "Alexander Wolff" wrote: Hi Rich, rather use *, not the comma between complex sumproduct terms. It is one of the many cases the favorable comma (10% faster) does not work. -- Moin+Gruss Alexander - MVP für MS Excel - www.xxcl.de - mso2000sp3 --7-2 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Mulitple SUMPRODUCTS?
Don,
Looks like your test worked, but not sure why mine doesn't. Could it be I am looking for a particular value in one column "a" and multiple values in another column "b" with the result being in column "c"? =SUMPRODUCT(--('[Normal Trial Balance.xls]3308 - Trial Balance 11-1-2006'!$C$2:$C$1500=A23),--('[Normal Trial Balance.xls]3308 - Trial Balance 11-1-2006'!$B$2:$B$1500={211,240})*'[Normal Trial Balance.xls]3308 - Trial Balance 11-1-2006'!$D$2:$D$1500) "Don Guillett" wrote: I just tested both of these where if c1:c11 contained either 1 or 3 the f1:f11 total is given. =SUMPRODUCT((C1:C11={1,3})*F1:F11) or =SUMPRODUCT(--(C1:C11={1,3})*F1:F11) -- Don Guillett SalesAid Software "RJMAT" wrote in message ... Thanks Don, I tried it, however, it came back with a #REF error. Any suggestions? =SUMPRODUCT(--('K:\0506FISC\SSOP''s June 06\[3308 - Trial Balance.xls]3308 - Trial Balance 11-1-2006'!$C$2:$C$1500=A23),--('K:\0506FISC\SSOP''s June 06\[3308 - Trial Balance.xls]3308 - Trial Balance 11-1-2006'!$B$2:$B$1500={211,240,245,252,253}),--('K:\0506FISC\SSOP''s June 06\[3308 - Trial Balance.xls]3308 - Trial Balance 11-1-2006'!$D$2:$D$1500)) "Don Guillett" wrote: ={211,215,330} -- Don Guillett SalesAid Software "RJMAT" wrote in message ... Hello, Please review the formula I have created below. Is there a way to have mulitple criteria's? Is this example, I used =211. I like to use multiple #'s. I don't think that nesting is the answser. Please advise. Rich =SUMPRODUCT(--('K:\0506FISC\SSOP''s June 06\[3308 - Trial Balance.xls]3308 - Trial Balance 11-1-2006'!$C$2:$C$1500=A23),--('K:\0506FISC\SSOP''s June 06\[3308 - Trial Balance.xls]3308 - Trial Balance 11-1-2006'!$B$2:$B$1500=211),--('K:\0506FISC\SSOP''s June 06\[3308 - Trial Balance.xls]3308 - Trial Balance 11-1-2006'!$D$2:$D$1500)) |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Mulitple SUMPRODUCTS?
Correction, it is giving a #value error.
SUMPRODUCT(--('[Normal Trial Balance.xls]3308 - Trial Balance 11-1-2006'!$C$2:$C$1500=A23),--('[Normal Trial Balance.xls]3308 - Trial Balance 11-1-2006'!$B$2:$B$1500={211,240,245,252,253}),--('[Normal Trial Balance.xls]3308 - Trial Balance 11-1-2006'!$D$2:$D$1500)) "Don Guillett" wrote: ={211,215,330} -- Don Guillett SalesAid Software "RJMAT" wrote in message ... Hello, Please review the formula I have created below. Is there a way to have mulitple criteria's? Is this example, I used =211. I like to use multiple #'s. I don't think that nesting is the answser. Please advise. Rich =SUMPRODUCT(--('K:\0506FISC\SSOP''s June 06\[3308 - Trial Balance.xls]3308 - Trial Balance 11-1-2006'!$C$2:$C$1500=A23),--('K:\0506FISC\SSOP''s June 06\[3308 - Trial Balance.xls]3308 - Trial Balance 11-1-2006'!$B$2:$B$1500=211),--('K:\0506FISC\SSOP''s June 06\[3308 - Trial Balance.xls]3308 - Trial Balance 11-1-2006'!$D$2:$D$1500)) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| multiple sumproducts and now formula is too long | Excel Discussion (Misc queries) | |||
| Dates and SumProducts | Excel Worksheet Functions | |||
| Problems with SUMPRODUCTS | Excel Worksheet Functions | |||
| SUMPRODUCTS | Excel Worksheet Functions | |||
| Sumproducts, Counta Lookup Ref Formulas | Excel Discussion (Misc queries) | |||
Hybrid Mode
