Mulitple SUMPRODUCTS?

R

RJMAT

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))
 
R

RJMAT

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 said:
={211,215,330}
--
Don Guillett
SalesAid Software
(e-mail address removed)
RJMAT said:
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))
 
R

RJMAT

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 said:
={211,215,330}
--
Don Guillett
SalesAid Software
(e-mail address removed)
RJMAT said:
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))
 
R

RJMAT

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 said:
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 said:
={211,215,330}
--
Don Guillett
SalesAid Software
(e-mail address removed)
RJMAT said:
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))
 
D

Don Guillett

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
(e-mail address removed)
RJMAT said:
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 said:
={211,215,330}
--
Don Guillett
SalesAid Software
(e-mail address removed)
RJMAT said:
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))
 
A

Alexander Wolff

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.
 
R

RJMAT

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 said:
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
(e-mail address removed)
RJMAT said:
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 said:
={211,215,330}
--
Don Guillett
SalesAid Software
(e-mail address removed)
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))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top