|
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)) |
Mulitple SUMPRODUCTS?
|
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)) |
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)) |
Mulitple SUMPRODUCTS?
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)) |
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 |
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)) |
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 |
| All times are GMT +1. The time now is 05:34 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com