Sumproduct is returning irregular values

Hi there

I am developing a skookum s/s where I am returning values based on various
criteria and am using sumproduct. The issue seems to be with the format of a
cell.

Here is one of my calculations.
=SUMPRODUCT((Actuals!$A$2:$A$7500"Q4")*(Actuals!$ D$2:$D$7500="40TKB")*(Actuals!$B$2:$B$7500=50)*Act uals!$J$2:$J$7500)

It is returning zero. However, if I put the 50 in quotes, it will return
the correct value. My issue tho is that some of the calculations don't need
quotes and some do. Eg, if I put "6531" in the following equation it will
return 0.
=SUMPRODUCT((Actuals!$A$2:$A$7500"Q3")*(Actuals!$ D$2:$D$7500="40TKB")*(Actuals!$F$2:$F$7500=6531)*A ctuals!$J$2:$J$7500)

I have not formatted the cells at all but they are exported from a home
grown system. I've played around with changing the format of the columns to
Number and general with no change. Any help would be great as I do not trust
my formulas as yet


--
Thanks
Marlaine

Some thoughts. Excel simply treats any TEXT as greater than the largest real
number in its numeric calculations. This can be misleading. If your intent is
to do pure lookup, stick to using index/match or vlookup for the job, do not
use sumproduct. And slant the core matching to be all text comparisons
(instead of trying to match by real numbers), set for exact matching. You
could use: =vlookup(A2&"",...) instead of: =vlookup(A2,...)
to convert numbers to text numbers (the: &"" bit will make it into text)
--
Max
Singapore
---
"Marlaine" wrote:
I am developing a skookum s/s where I am returning values based on various
criteria and am using sumproduct. The issue seems to be with the format of a
cell.

Here is one of my calculations.
=SUMPRODUCT((Actuals!$A$2:$A$7500"Q4")*(Actuals!$ D$2:$D$7500="40TKB")*(Actuals!$B$2:$B$7500=50)*Act uals!$J$2:$J$7500)

It is returning zero. However, if I put the 50 in quotes, it will return
the correct value. My issue tho is that some of the calculations don't need
quotes and some do. Eg, if I put "6531" in the following equation it will
return 0.
=SUMPRODUCT((Actuals!$A$2:$A$7500"Q3")*(Actuals!$ D$2:$D$7500="40TKB")*(Actuals!$F$2:$F$7500=6531)*A ctuals!$J$2:$J$7500)

I have not formatted the cells at all but they are exported from a home
grown system. I've played around with changing the format of the columns to
Number and general with no change. Any help would be great as I do not trust
my formulas as yet


--
Thanks
Marlaine

I've been using Sumproduct quite a lot lately and would have used the 50
without quotes (provided it refers to numeric data), so I think the problem
may be with one of the other conditions - have you tried variations of these
e.g. ="Q4" for condition 1 instead of "Q4" (assuming A2:A7500 contains
Q4-type alphnumeric data, rather than Q4 being a cell reference).

Try a look here also: http://www.xldynamic.com/source/xld.SUMPRODUCT.html

If you'd like to send a copy of your workbook, let me know.

Regards,

Tom-S

"Marlaine" wrote:

Hi there

I am developing a skookum s/s where I am returning values based on various
criteria and am using sumproduct. The issue seems to be with the format of a
cell.

Here is one of my calculations.
=SUMPRODUCT((Actuals!$A$2:$A$7500"Q4")*(Actuals!$ D$2:$D$7500="40TKB")*(Actuals!$B$2:$B$7500=50)*Act uals!$J$2:$J$7500)

It is returning zero. However, if I put the 50 in quotes, it will return
the correct value. My issue tho is that some of the calculations don't need
quotes and some do. Eg, if I put "6531" in the following equation it will
return 0.
=SUMPRODUCT((Actuals!$A$2:$A$7500"Q3")*(Actuals!$ D$2:$D$7500="40TKB")*(Actuals!$F$2:$F$7500=6531)*A ctuals!$J$2:$J$7500)

I have not formatted the cells at all but they are exported from a home
grown system. I've played around with changing the format of the columns to
Number and general with no change. Any help would be great as I do not trust
my formulas as yet


--
Thanks
Marlaine

Thanks for the link. It looks like I am using sumproduct incorrectly. What
I am trying to do is:
- sum all the values in column J if column A is = to Q1, Q2 or Q3, and if
column B is equal to 50.
- there are some instances where I would need to sum all the values in
column J if column A is = to Q1, Q2 or Q3, and if column B is equal to 50,
and if column B is equal to 51.

The values would change of course depending on what cell or sheet I am in.

I've noticed that sometimes the answers are abbreviated. Being a bit of a
novice I dont quite understand the intent so if you could do a long answer to
the problem above, that would be wonderful.

Another thing I dont understand and which appears to be part of the issue
above, is how to tell whether a column is numeric or general and why nothing
changed in my searches when I changed the format from general to numeric.

--
Thank you very much
Marlaine


"Tom-S" wrote:

I've been using Sumproduct quite a lot lately and would have used the 50
without quotes (provided it refers to numeric data), so I think the problem
may be with one of the other conditions - have you tried variations of these
e.g. ="Q4" for condition 1 instead of "Q4" (assuming A2:A7500 contains
Q4-type alphnumeric data, rather than Q4 being a cell reference).

Try a look here also: http://www.xldynamic.com/source/xld.SUMPRODUCT.html

If you'd like to send a copy of your workbook, let me know.

Regards,

Tom-S

"Marlaine" wrote:

Hi there

I am developing a skookum s/s where I am returning values based on various
criteria and am using sumproduct. The issue seems to be with the format of a
cell.

Here is one of my calculations.
=SUMPRODUCT((Actuals!$A$2:$A$7500"Q4")*(Actuals!$ D$2:$D$7500="40TKB")*(Actuals!$B$2:$B$7500=50)*Act uals!$J$2:$J$7500)

It is returning zero. However, if I put the 50 in quotes, it will return
the correct value. My issue tho is that some of the calculations don't need
quotes and some do. Eg, if I put "6531" in the following equation it will
return 0.
=SUMPRODUCT((Actuals!$A$2:$A$7500"Q3")*(Actuals!$ D$2:$D$7500="40TKB")*(Actuals!$F$2:$F$7500=6531)*A ctuals!$J$2:$J$7500)

I have not formatted the cells at all but they are exported from a home
grown system. I've played around with changing the format of the columns to
Number and general with no change. Any help would be great as I do not trust
my formulas as yet


--
Thanks
Marlaine

oh - I posted too soon. Sumproduct is the way to go. I am having another
read through the link.
--
Thanks
Marlaine


"Tom-S" wrote:

I've been using Sumproduct quite a lot lately and would have used the 50
without quotes (provided it refers to numeric data), so I think the problem
may be with one of the other conditions - have you tried variations of these
e.g. ="Q4" for condition 1 instead of "Q4" (assuming A2:A7500 contains
Q4-type alphnumeric data, rather than Q4 being a cell reference).

Try a look here also: http://www.xldynamic.com/source/xld.SUMPRODUCT.html

If you'd like to send a copy of your workbook, let me know.

Regards,

Tom-S

"Marlaine" wrote:

Hi there

I am developing a skookum s/s where I am returning values based on various
criteria and am using sumproduct. The issue seems to be with the format of a
cell.

Here is one of my calculations.
=SUMPRODUCT((Actuals!$A$2:$A$7500"Q4")*(Actuals!$ D$2:$D$7500="40TKB")*(Actuals!$B$2:$B$7500=50)*Act uals!$J$2:$J$7500)

It is returning zero. However, if I put the 50 in quotes, it will return
the correct value. My issue tho is that some of the calculations don't need
quotes and some do. Eg, if I put "6531" in the following equation it will
return 0.
=SUMPRODUCT((Actuals!$A$2:$A$7500"Q3")*(Actuals!$ D$2:$D$7500="40TKB")*(Actuals!$F$2:$F$7500=6531)*A ctuals!$J$2:$J$7500)

I have not formatted the cells at all but they are exported from a home
grown system. I've played around with changing the format of the columns to
Number and general with no change. Any help would be great as I do not trust
my formulas as yet


--
Thanks
Marlaine

Sorry about the confusion earlier. I have read the link and it explains
things very well. I like sumproduct better then sumifs because it seems like
you dont have to add error conditions, eg isna - dont understand those so not
sure if that statement made sense.

I am getting the expected values in my formulas so thank you. There are
still two things I do not understand though.

1) If I am looking up the value 50 in column B, it needs to be in quotes.
If I am looking up the value 6938 in column F, I do not need quotes. Why the
difference? Column B is calculated from Column F by the Left formula

2) I originally had
=SUMPRODUCT((Actuals!$A$2:$A$7500="Q1")*(Actuals!$ A$2:$A$7500="Q2"..... but
it would return zero. Can you only have one lookup per column in the formula?
--
Thanks
Marlaine


"Tom-S" wrote:

I've been using Sumproduct quite a lot lately and would have used the 50
without quotes (provided it refers to numeric data), so I think the problem
may be with one of the other conditions - have you tried variations of these
e.g. ="Q4" for condition 1 instead of "Q4" (assuming A2:A7500 contains
Q4-type alphnumeric data, rather than Q4 being a cell reference).

Try a look here also: http://www.xldynamic.com/source/xld.SUMPRODUCT.html

If you'd like to send a copy of your workbook, let me know.

Regards,

Tom-S

"Marlaine" wrote:

Hi there

I am developing a skookum s/s where I am returning values based on various
criteria and am using sumproduct. The issue seems to be with the format of a
cell.

Here is one of my calculations.
=SUMPRODUCT((Actuals!$A$2:$A$7500"Q4")*(Actuals!$ D$2:$D$7500="40TKB")*(Actuals!$B$2:$B$7500=50)*Act uals!$J$2:$J$7500)

It is returning zero. However, if I put the 50 in quotes, it will return
the correct value. My issue tho is that some of the calculations don't need
quotes and some do. Eg, if I put "6531" in the following equation it will
return 0.
=SUMPRODUCT((Actuals!$A$2:$A$7500"Q3")*(Actuals!$ D$2:$D$7500="40TKB")*(Actuals!$F$2:$F$7500=6531)*A ctuals!$J$2:$J$7500)

I have not formatted the cells at all but they are exported from a home
grown system. I've played around with changing the format of the columns to
Number and general with no change. Any help would be great as I do not trust
my formulas as yet


--
Thanks
Marlaine

I haven't tried the sumifs myself yet as I'm still fairly attached to Excel
2003, which doesn't have sumifs, but I hear what you're saying about the
isna's.

Your other 2 questions:

1) It's the use of the LEFT function, which creates a text string as its
result, so you have to use "50" rather than 50 in the sumproduct function.

(A simple test: On a new sheet, format cells B2 and C2 to numbers with 2
d.p.; type a number, say 810 in cell C2; type this formula in cell B2
=LEFT(C2,2)
and this formula in cell B4 =IF(ISTEXT(B2),"txt","num")
cell B2 will display 81 (with no decimal places) and cell B4 will display
'txt' (because the content of cell B2 is a text string); if you then type the
number 81 directly into cell B2, you should see cell B4 display 'num'
instead.)

2) Actually, the sumproduct function here was working as it should by
returning zero as the answer. Say you were only looking at data in A1:A5 and
the data was Q1,Q2,Q2,Q1,Q2; the first condition (=Q1) would return an array
{1,0,0,1,0} and the second condition (=Q2) would return an array {0,1,1,0,1};
when these arrays are multiplied together the result is zero; this result was
due to the particular conditions set in the formula rather than the 'lookup'
referring to the same column of data as such.

Hope that helps. Let me know if there's more.

Regards,

Tom


"Marlaine" wrote:

Sorry about the confusion earlier. I have read the link and it explains
things very well. I like sumproduct better then sumifs because it seems like
you dont have to add error conditions, eg isna - dont understand those so not
sure if that statement made sense.

I am getting the expected values in my formulas so thank you. There are
still two things I do not understand though.

1) If I am looking up the value 50 in column B, it needs to be in quotes.
If I am looking up the value 6938 in column F, I do not need quotes. Why the
difference? Column B is calculated from Column F by the Left formula

2) I originally had
=SUMPRODUCT((Actuals!$A$2:$A$7500="Q1")*(Actuals!$ A$2:$A$7500="Q2"..... but
it would return zero. Can you only have one lookup per column in the formula?
--
Thanks
Marlaine


"Tom-S" wrote:

I've been using Sumproduct quite a lot lately and would have used the 50
without quotes (provided it refers to numeric data), so I think the problem
may be with one of the other conditions - have you tried variations of these
e.g. ="Q4" for condition 1 instead of "Q4" (assuming A2:A7500 contains
Q4-type alphnumeric data, rather than Q4 being a cell reference).

Try a look here also: http://www.xldynamic.com/source/xld.SUMPRODUCT.html

If you'd like to send a copy of your workbook, let me know.

Regards,

Tom-S

"Marlaine" wrote:

Hi there

I am developing a skookum s/s where I am returning values based on various
criteria and am using sumproduct. The issue seems to be with the format of a
cell.

Here is one of my calculations.
=SUMPRODUCT((Actuals!$A$2:$A$7500"Q4")*(Actuals!$ D$2:$D$7500="40TKB")*(Actuals!$B$2:$B$7500=50)*Act uals!$J$2:$J$7500)

It is returning zero. However, if I put the 50 in quotes, it will return
the correct value. My issue tho is that some of the calculations don't need
quotes and some do. Eg, if I put "6531" in the following equation it will
return 0.
=SUMPRODUCT((Actuals!$A$2:$A$7500"Q3")*(Actuals!$ D$2:$D$7500="40TKB")*(Actuals!$F$2:$F$7500=6531)*A ctuals!$J$2:$J$7500)

I have not formatted the cells at all but they are exported from a home
grown system. I've played around with changing the format of the columns to
Number and general with no change. Any help would be great as I do not trust
my formulas as yet


--
Thanks
Marlaine

Thank you Tom. Great info. I understand your answer to question 2. How
then would I format the formula if I wanted to sum up Q1 and Q3? I expect
you would need an or. Something like
=SUMPRODUCT(OR(Actuals!$A$2:$A$7500="Q1"),(Actuals !$A$2:$A$7500="Q3"))*(.....

--
Thanks
Marlaine


"Tom-S" wrote:

I haven't tried the sumifs myself yet as I'm still fairly attached to Excel
2003, which doesn't have sumifs, but I hear what you're saying about the
isna's.

Your other 2 questions:

1) It's the use of the LEFT function, which creates a text string as its
result, so you have to use "50" rather than 50 in the sumproduct function.

(A simple test: On a new sheet, format cells B2 and C2 to numbers with 2
d.p.; type a number, say 810 in cell C2; type this formula in cell B2
=LEFT(C2,2)
and this formula in cell B4 =IF(ISTEXT(B2),"txt","num")
cell B2 will display 81 (with no decimal places) and cell B4 will display
'txt' (because the content of cell B2 is a text string); if you then type the
number 81 directly into cell B2, you should see cell B4 display 'num'
instead.)

2) Actually, the sumproduct function here was working as it should by
returning zero as the answer. Say you were only looking at data in A1:A5 and
the data was Q1,Q2,Q2,Q1,Q2; the first condition (=Q1) would return an array
{1,0,0,1,0} and the second condition (=Q2) would return an array {0,1,1,0,1};
when these arrays are multiplied together the result is zero; this result was
due to the particular conditions set in the formula rather than the 'lookup'
referring to the same column of data as such.

Hope that helps. Let me know if there's more.

Regards,

Tom


"Marlaine" wrote:

Sorry about the confusion earlier. I have read the link and it explains
things very well. I like sumproduct better then sumifs because it seems like
you dont have to add error conditions, eg isna - dont understand those so not
sure if that statement made sense.

I am getting the expected values in my formulas so thank you. There are
still two things I do not understand though.

1) If I am looking up the value 50 in column B, it needs to be in quotes.
If I am looking up the value 6938 in column F, I do not need quotes. Why the
difference? Column B is calculated from Column F by the Left formula

2) I originally had
=SUMPRODUCT((Actuals!$A$2:$A$7500="Q1")*(Actuals!$ A$2:$A$7500="Q2"..... but
it would return zero. Can you only have one lookup per column in the formula?
--
Thanks
Marlaine


"Tom-S" wrote:

I've been using Sumproduct quite a lot lately and would have used the 50
without quotes (provided it refers to numeric data), so I think the problem
may be with one of the other conditions - have you tried variations of these
e.g. ="Q4" for condition 1 instead of "Q4" (assuming A2:A7500 contains
Q4-type alphnumeric data, rather than Q4 being a cell reference).

Try a look here also: http://www.xldynamic.com/source/xld.SUMPRODUCT.html

If you'd like to send a copy of your workbook, let me know.

Regards,

Tom-S

"Marlaine" wrote:

Hi there

I am developing a skookum s/s where I am returning values based on various
criteria and am using sumproduct. The issue seems to be with the format of a
cell.

Here is one of my calculations.
=SUMPRODUCT((Actuals!$A$2:$A$7500"Q4")*(Actuals!$ D$2:$D$7500="40TKB")*(Actuals!$B$2:$B$7500=50)*Act uals!$J$2:$J$7500)

It is returning zero. However, if I put the 50 in quotes, it will return
the correct value. My issue tho is that some of the calculations don't need
quotes and some do. Eg, if I put "6531" in the following equation it will
return 0.
=SUMPRODUCT((Actuals!$A$2:$A$7500"Q3")*(Actuals!$ D$2:$D$7500="40TKB")*(Actuals!$F$2:$F$7500=6531)*A ctuals!$J$2:$J$7500)

I have not formatted the cells at all but they are exported from a home
grown system. I've played around with changing the format of the columns to
Number and general with no change. Any help would be great as I do not trust
my formulas as yet


--
Thanks
Marlaine

Sorry for delay in replying.

I found this out from the link (posted earlier) and have tested to make sure
it works. Toward the end of the link are some notes and note number 1 shows
how to test for 2 or more values from the same range. So if you wanted to
test for "Q1" or "Q3" from $A$2:$A$7500 it would be:

=SUMPRODUCT((Actuals!$A$2:$A$7500={"Q1","Q3"})*(.. .))

I believe the curly brackets { } are called 'braces', and they're typically
found above the square brackets [ ] on the keyboard.

Regards,

Tom


"Marlaine" wrote:

Thank you Tom. Great info. I understand your answer to question 2. How
then would I format the formula if I wanted to sum up Q1 and Q3? I expect
you would need an or. Something like
=SUMPRODUCT(OR(Actuals!$A$2:$A$7500="Q1"),(Actuals !$A$2:$A$7500="Q3"))*(.....

--
Thanks
Marlaine


"Tom-S" wrote:

I haven't tried the sumifs myself yet as I'm still fairly attached to Excel
2003, which doesn't have sumifs, but I hear what you're saying about the
isna's.

Your other 2 questions:

1) It's the use of the LEFT function, which creates a text string as its
result, so you have to use "50" rather than 50 in the sumproduct function.

(A simple test: On a new sheet, format cells B2 and C2 to numbers with 2
d.p.; type a number, say 810 in cell C2; type this formula in cell B2
=LEFT(C2,2)
and this formula in cell B4 =IF(ISTEXT(B2),"txt","num")
cell B2 will display 81 (with no decimal places) and cell B4 will display
'txt' (because the content of cell B2 is a text string); if you then type the
number 81 directly into cell B2, you should see cell B4 display 'num'
instead.)

2) Actually, the sumproduct function here was working as it should by
returning zero as the answer. Say you were only looking at data in A1:A5 and
the data was Q1,Q2,Q2,Q1,Q2; the first condition (=Q1) would return an array
{1,0,0,1,0} and the second condition (=Q2) would return an array {0,1,1,0,1};
when these arrays are multiplied together the result is zero; this result was
due to the particular conditions set in the formula rather than the 'lookup'
referring to the same column of data as such.

Hope that helps. Let me know if there's more.

Regards,

Tom


"Marlaine" wrote:

Sorry about the confusion earlier. I have read the link and it explains
things very well. I like sumproduct better then sumifs because it seems like
you dont have to add error conditions, eg isna - dont understand those so not
sure if that statement made sense.

I am getting the expected values in my formulas so thank you. There are
still two things I do not understand though.

1) If I am looking up the value 50 in column B, it needs to be in quotes.
If I am looking up the value 6938 in column F, I do not need quotes. Why the
difference? Column B is calculated from Column F by the Left formula

2) I originally had
=SUMPRODUCT((Actuals!$A$2:$A$7500="Q1")*(Actuals!$ A$2:$A$7500="Q2"..... but
it would return zero. Can you only have one lookup per column in the formula?
--
Thanks
Marlaine


"Tom-S" wrote:

I've been using Sumproduct quite a lot lately and would have used the 50
without quotes (provided it refers to numeric data), so I think the problem
may be with one of the other conditions - have you tried variations of these
e.g. ="Q4" for condition 1 instead of "Q4" (assuming A2:A7500 contains
Q4-type alphnumeric data, rather than Q4 being a cell reference).

Try a look here also: http://www.xldynamic.com/source/xld.SUMPRODUCT.html

If you'd like to send a copy of your workbook, let me know.

Regards,

Tom-S

"Marlaine" wrote:

Hi there

I am developing a skookum s/s where I am returning values based on various
criteria and am using sumproduct. The issue seems to be with the format of a
cell.

Here is one of my calculations.
=SUMPRODUCT((Actuals!$A$2:$A$7500"Q4")*(Actuals!$ D$2:$D$7500="40TKB")*(Actuals!$B$2:$B$7500=50)*Act uals!$J$2:$J$7500)

It is returning zero. However, if I put the 50 in quotes, it will return
the correct value. My issue tho is that some of the calculations don't need
quotes and some do. Eg, if I put "6531" in the following equation it will
return 0.
=SUMPRODUCT((Actuals!$A$2:$A$7500"Q3")*(Actuals!$ D$2:$D$7500="40TKB")*(Actuals!$F$2:$F$7500=6531)*A ctuals!$J$2:$J$7500)

I have not formatted the cells at all but they are exported from a home
grown system. I've played around with changing the format of the columns to
Number and general with no change. Any help would be great as I do not trust
my formulas as yet


--
Thanks
Marlaine

Your original test, this term: (Actuals!$A$2:$A$7500"Q4")
was meaningless because "" is an operator meant for numbers, not text. "Q4"
is text, and unfortunately, because of the way that Excel evaluates it as
clarified in my response, the results can be misleading. Just a parting
thought here for you.