Sumproduct is returning irregular values

[Marlaine]

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)*Actuals!$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)*Actuals!$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

 

[Max]

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)

 

[Tom-S]

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]

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.

 

[Marlaine]

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

 

[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?

 

[Tom-S]

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]

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"))*(.....

 

[Tom-S]

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

 

[Max]

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.

 

[Marlaine]

Thank you very much Tom. I am good to go now.
--
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

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"))*(.....

 

[Marlaine]

Thanks Max. I did see that early and have changed my calculated values from
Q1, Q2 etc to 1, 2, 3 4 so that the equation will always work. I just kept
copying and pasting my original formula during the queries.

The replies in this forum are extremely helpful and educational!