SUMPRODUCT with Multiple Criteria

K

Kelly

I have three criteria with this formula and it brings back a $0 value, when I
know there is a $ amount that should have be calculated. I am working in one
workbook and linking to another. What have I done wrong?

=SUMPRODUCT(--('[Workbook Name.xls]Name'!$O$2:$O$37="P"),--('[Workbook
Name.xls]Name'!$O$2:$O$37="A"),--('[Workbook
Name.xls]Name'!$M$2:$M$37="Y"),'[Workbook Name.xls]Name'!$I$2:$I$37)
 
T

T. Valko

You're testing one range twice for 2 different criteria. While one test
might be true the other *has* to be false so T*F=0. In other words: if it's
"A" then it *can't* be "P" and vice versa.

Try it like this:

=SUMPRODUCT(('[Workbook Name.xls]Name'!$O$2:$O$37={"A","P"})*('[Workbook
Name.xls]Name'!$M$2:$M$37="Y")*'[Workbook Name.xls]Name'!$I$2:$I$37)
 
K

Kelly

You are brilliant!! It worked! Thank you for the help.

T. Valko said:
You're testing one range twice for 2 different criteria. While one test
might be true the other *has* to be false so T*F=0. In other words: if it's
"A" then it *can't* be "P" and vice versa.

Try it like this:

=SUMPRODUCT(('[Workbook Name.xls]Name'!$O$2:$O$37={"A","P"})*('[Workbook
Name.xls]Name'!$M$2:$M$37="Y")*'[Workbook Name.xls]Name'!$I$2:$I$37)


--
Biff
Microsoft Excel MVP


Kelly said:
I have three criteria with this formula and it brings back a $0 value, when
I
know there is a $ amount that should have be calculated. I am working in
one
workbook and linking to another. What have I done wrong?

=SUMPRODUCT(--('[Workbook Name.xls]Name'!$O$2:$O$37="P"),--('[Workbook
Name.xls]Name'!$O$2:$O$37="A"),--('[Workbook
Name.xls]Name'!$M$2:$M$37="Y"),'[Workbook Name.xls]Name'!$I$2:$I$37)
Click to expand...
Click to expand...
 
T

T. Valko

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


Kelly said:
You are brilliant!! It worked! Thank you for the help.

T. Valko said:
You're testing one range twice for 2 different criteria. While one test
might be true the other *has* to be false so T*F=0. In other words: if
it's
"A" then it *can't* be "P" and vice versa.

Try it like this:

=SUMPRODUCT(('[Workbook Name.xls]Name'!$O$2:$O$37={"A","P"})*('[Workbook
Name.xls]Name'!$M$2:$M$37="Y")*'[Workbook Name.xls]Name'!$I$2:$I$37)


--
Biff
Microsoft Excel MVP


Kelly said:
I have three criteria with this formula and it brings back a $0 value,
when
I
know there is a $ amount that should have be calculated. I am working
in
one
workbook and linking to another. What have I done wrong?

=SUMPRODUCT(--('[Workbook Name.xls]Name'!$O$2:$O$37="P"),--('[Workbook
Name.xls]Name'!$O$2:$O$37="A"),--('[Workbook
Name.xls]Name'!$M$2:$M$37="Y"),'[Workbook Name.xls]Name'!$I$2:$I$37)
Click to expand...
Click to expand...
Click to expand...
 
K

Kelly

One other question hopefully you can help with; I have a similar situation
with having to count the number of occurences with three criteria (two of
which are in the same column). I tried to modify my formula, but it isn't
working.

=SUMPRODUCT(--('[Workbook Name.xls]Name'!$O$2:$O$37={"A","P"}),--('[Workbook
Name.xls]Name'!$F$2:$F$37="1"))

T. Valko said:
You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


Kelly said:
You are brilliant!! It worked! Thank you for the help.

T. Valko said:
You're testing one range twice for 2 different criteria. While one test
might be true the other *has* to be false so T*F=0. In other words: if
it's
"A" then it *can't* be "P" and vice versa.

Try it like this:

=SUMPRODUCT(('[Workbook Name.xls]Name'!$O$2:$O$37={"A","P"})*('[Workbook
Name.xls]Name'!$M$2:$M$37="Y")*'[Workbook Name.xls]Name'!$I$2:$I$37)


--
Biff
Microsoft Excel MVP


I have three criteria with this formula and it brings back a $0 value,
when
I
know there is a $ amount that should have be calculated. I am working
in
one
workbook and linking to another. What have I done wrong?

=SUMPRODUCT(--('[Workbook Name.xls]Name'!$O$2:$O$37="P"),--('[Workbook
Name.xls]Name'!$O$2:$O$37="A"),--('[Workbook
Name.xls]Name'!$M$2:$M$37="Y"),'[Workbook Name.xls]Name'!$I$2:$I$37)
Click to expand...
Click to expand...
Click to expand...
 
B

Bob Phillips

Probably a number, not text

=SUMPRODUCT(--('[Workbook Name.xls]Name'!$O$2:$O$37={"A","P"}),
--('[Workbook Name.xls]Name'!$F$2:$F$37=1))


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



Kelly said:
One other question hopefully you can help with; I have a similar situation
with having to count the number of occurences with three criteria (two of
which are in the same column). I tried to modify my formula, but it isn't
working.

=SUMPRODUCT(--('[Workbook
Name.xls]Name'!$O$2:$O$37={"A","P"}),--('[Workbook
Name.xls]Name'!$F$2:$F$37="1"))

T. Valko said:
You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


Kelly said:
You are brilliant!! It worked! Thank you for the help.

:

You're testing one range twice for 2 different criteria. While one
test
might be true the other *has* to be false so T*F=0. In other words: if
it's
"A" then it *can't* be "P" and vice versa.

Try it like this:

=SUMPRODUCT(('[Workbook
Name.xls]Name'!$O$2:$O$37={"A","P"})*('[Workbook
Name.xls]Name'!$M$2:$M$37="Y")*'[Workbook Name.xls]Name'!$I$2:$I$37)


--
Biff
Microsoft Excel MVP


I have three criteria with this formula and it brings back a $0
value,
when
I
know there is a $ amount that should have be calculated. I am
working
in
one
workbook and linking to another. What have I done wrong?

=SUMPRODUCT(--('[Workbook
Name.xls]Name'!$O$2:$O$37="P"),--('[Workbook
Name.xls]Name'!$O$2:$O$37="A"),--('[Workbook
Name.xls]Name'!$M$2:$M$37="Y"),'[Workbook Name.xls]Name'!$I$2:$I$37)
Click to expand...
Click to expand...
Click to expand...
 
K

Kelly

I am getting a #VALUE error. Any other suggestions?



Bob Phillips said:
Probably a number, not text

=SUMPRODUCT(--('[Workbook Name.xls]Name'!$O$2:$O$37={"A","P"}),
--('[Workbook Name.xls]Name'!$F$2:$F$37=1))


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



Kelly said:
One other question hopefully you can help with; I have a similar situation
with having to count the number of occurences with three criteria (two of
which are in the same column). I tried to modify my formula, but it isn't
working.

=SUMPRODUCT(--('[Workbook
Name.xls]Name'!$O$2:$O$37={"A","P"}),--('[Workbook
Name.xls]Name'!$F$2:$F$37="1"))

T. Valko said:
You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


You are brilliant!! It worked! Thank you for the help.

:

You're testing one range twice for 2 different criteria. While one
test
might be true the other *has* to be false so T*F=0. In other words: if
it's
"A" then it *can't* be "P" and vice versa.

Try it like this:

=SUMPRODUCT(('[Workbook
Name.xls]Name'!$O$2:$O$37={"A","P"})*('[Workbook
Name.xls]Name'!$M$2:$M$37="Y")*'[Workbook Name.xls]Name'!$I$2:$I$37)


--
Biff
Microsoft Excel MVP


I have three criteria with this formula and it brings back a $0
value,
when
I
know there is a $ amount that should have be calculated. I am
working
in
one
workbook and linking to another. What have I done wrong?

=SUMPRODUCT(--('[Workbook
Name.xls]Name'!$O$2:$O$37="P"),--('[Workbook
Name.xls]Name'!$O$2:$O$37="A"),--('[Workbook
Name.xls]Name'!$M$2:$M$37="Y"),'[Workbook Name.xls]Name'!$I$2:$I$37)
Click to expand...
Click to expand...
Click to expand...
 
B

Bob Phillips

that suggests you have an error in one of the cells being summed, probably
F2:F37

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



Kelly said:
I am getting a #VALUE error. Any other suggestions?



Bob Phillips said:
Probably a number, not text

=SUMPRODUCT(--('[Workbook Name.xls]Name'!$O$2:$O$37={"A","P"}),
--('[Workbook
Name.xls]Name'!$F$2:$F$37=1))


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



Kelly said:
One other question hopefully you can help with; I have a similar
situation
with having to count the number of occurences with three criteria (two
of
which are in the same column). I tried to modify my formula, but it
isn't
working.

=SUMPRODUCT(--('[Workbook
Name.xls]Name'!$O$2:$O$37={"A","P"}),--('[Workbook
Name.xls]Name'!$F$2:$F$37="1"))

:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


You are brilliant!! It worked! Thank you for the help.

:

You're testing one range twice for 2 different criteria. While one
test
might be true the other *has* to be false so T*F=0. In other words:
if
it's
"A" then it *can't* be "P" and vice versa.

Try it like this:

=SUMPRODUCT(('[Workbook
Name.xls]Name'!$O$2:$O$37={"A","P"})*('[Workbook
Name.xls]Name'!$M$2:$M$37="Y")*'[Workbook
Name.xls]Name'!$I$2:$I$37)


--
Biff
Microsoft Excel MVP


I have three criteria with this formula and it brings back a $0
value,
when
I
know there is a $ amount that should have be calculated. I am
working
in
one
workbook and linking to another. What have I done wrong?

=SUMPRODUCT(--('[Workbook
Name.xls]Name'!$O$2:$O$37="P"),--('[Workbook
Name.xls]Name'!$O$2:$O$37="A"),--('[Workbook
Name.xls]Name'!$M$2:$M$37="Y"),'[Workbook
Name.xls]Name'!$I$2:$I$37)
Click to expand...
Click to expand...
Click to expand...
 
K

Kelly

Figured it out:

=SUMPRODUCT(('[Workbook Name.xls]Name'!$O$2:$O$37={"A","P"})*('[Workbook
Name.xls]Name'!$F$2:$F$37=1))



Bob Phillips said:
that suggests you have an error in one of the cells being summed, probably
F2:F37

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



Kelly said:
I am getting a #VALUE error. Any other suggestions?



Bob Phillips said:
Probably a number, not text

=SUMPRODUCT(--('[Workbook Name.xls]Name'!$O$2:$O$37={"A","P"}),
--('[Workbook
Name.xls]Name'!$F$2:$F$37=1))


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



One other question hopefully you can help with; I have a similar
situation
with having to count the number of occurences with three criteria (two
of
which are in the same column). I tried to modify my formula, but it
isn't
working.

=SUMPRODUCT(--('[Workbook
Name.xls]Name'!$O$2:$O$37={"A","P"}),--('[Workbook
Name.xls]Name'!$F$2:$F$37="1"))

:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


You are brilliant!! It worked! Thank you for the help.

:

You're testing one range twice for 2 different criteria. While one
test
might be true the other *has* to be false so T*F=0. In other words:
if
it's
"A" then it *can't* be "P" and vice versa.

Try it like this:

=SUMPRODUCT(('[Workbook
Name.xls]Name'!$O$2:$O$37={"A","P"})*('[Workbook
Name.xls]Name'!$M$2:$M$37="Y")*'[Workbook
Name.xls]Name'!$I$2:$I$37)


--
Biff
Microsoft Excel MVP


I have three criteria with this formula and it brings back a $0
value,
when
I
know there is a $ amount that should have be calculated. I am
working
in
one
workbook and linking to another. What have I done wrong?

=SUMPRODUCT(--('[Workbook
Name.xls]Name'!$O$2:$O$37="P"),--('[Workbook
Name.xls]Name'!$O$2:$O$37="A"),--('[Workbook
Name.xls]Name'!$M$2:$M$37="Y"),'[Workbook
Name.xls]Name'!$I$2:$I$37)
Click to expand...
Click to expand...
Click to expand...
 

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

Similar Threads

Scrabble Value calculation for Welsh words 0
SUMProduct Part II 5
VLOOKUP and SUMPRODUCT 1
Percentile on Aggregate data with multiple criteria 0
IF/COUNT/AVERAGE... Value returned is incorrect. 3
SUMPRODUCT or COUNTIF - Multiple Criteria 2
Retrieve unique items with 2 criteria (USING SUMPRODUCT) 5
SUMPRODUCT w/ 3 criteria 3

Top