Sumproduct is returning irregular values

Thank you very much Tom. I am good to go now.
--
Thanks
Marlaine


"Tom-S" wrote:

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

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!
--
Thanks
Marlaine


"Max" wrote:

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.


.