Sumproduct Question

[JCS]

Hi All,

I've been experiementing with the Sumproduct function in Excel 2007 and ran
across a problem with the function that I cannot figure out. The following
is an example: I have the following table in cells E21 to G23:

1 5 1
6 10 2
11 15 3


In cell D21 I input a value (e.g. 8)
In cell E25 I have the followng formula:
=SUMPRODUCT((E21:E23>=D21)*(F21:F23<=D21)*(G21:G23))
In cell D21, if I enter 8 I should see 2 - instead I get 0. Any ideas on
what could be wrong? Or would it be better to use another function? Thanks
in advance!!

John

 

[Gary''s Student]

0 is correct

After all, the E-factor will only pull from the last row and the F-factor
will only pull from the first row.

 

[Huber57]

John,
If you want the result of 2, put a plus (+) sign instead of the first
multiplication (*) sign.

=SUMPRODUCT((E21:E23>=D21)+(F21:F23<=D21)*(G21:G23))

Sincerely,

Doug

 

[JCS]

Hi Doug,

You nailed it! Thank you! I gotta tell ya, that I thought I tried it and
that it didn't work. I've tried so many variations I don't remember what
exactly i tried. I've worked with the Sumproduct function before and have
had no problems. Could you briefly give me an expalanation of what your
solution does?

Thanks!
John

 

[JCS]

Hi,

I got it excited to quickly. Look's like Gary is right. When I enter a 1 I
get 3 instead of 1 or if I enter 11 I get 4 instead of 3. OK, I give up what
is a better way (if any) to do this?

John

 

[Joe User]

=SUMPRODUCT((E21:E23>=D21)*(F21:F23<=D21)*(G21:G23))
In cell D21, if I enter 8 I should see 2 - instead
I get 0. Any ideas on what could be wrong?

I think your logical expression is wrong. It tests whether F21<=D21<=E21,
for example. I think you want to test whether E21<=D21<=F21. To wit:

=SUMPRODUCT((E21:E23<=D21)*(D1<=F21:F23),G21:G23)

The minor additional syntax changes might help to write such formulas
correctlyl in the future. Separating the range G21:G23 is good practice. It
will work even if some of G21:G23 are non-numeric.


----- original message -----

 

[JCS]

Hi,

This time I tested your solution thorougly and it works great - as I hoped
it would. Thank you so much. Also, thanks for the syntax lesson. I learned
something today. One question, What do you mean by separating G21:G23. I
substitued 1 2 and 3 with a b and c and my result was 0 everytime. I may
want to do this one day. What am i doing wrong? Again, many thanks!!

John

 

[Joe User]

What do you mean by separating G21:G23.

Yeah, that wasn't clear. I was rushed.

I meant: specifying the range as a separate argument. That is, using
",G21:G23" instead of "*G21:G23".

The difference can seen when any cell in a range contains text. (A common
example is the null string, "".)

For a simple demonstration, fill A1:A3, B1:B3 and C1:C3 with the number 1 in
each cell. Then enter the formula =SUMPRODUCT((A1:A3=1)*(B1:B3=1)*C1:C3).
The result should be 3.

Now enter "abc" into C2. The SUMPRODUCT formula above will return a #VALUE
error.

Now change the formula to =SUMPRODUCT((A1:A3=1)*(B1:B3=1),C1:C3). The
result should be 2, despite "abc" in C2.

The explanation is: SUMPRODUCT treats text values in arrays as zero,
whereas Excel treats some text in arithmetic expressions as an error.

On the other hand, text in arithmetic expressions that matches Excel's idea
of a number -- which includes date and time -- is treated as a number.
However, SUMPRODUCT does not make that distinction. Klunk!

So you need to look at the circumstances in order to decide if it is better
to use a range in an arithmetic expression or to specify it as a separate
argument.

Did that clear things up? It shouldn't have! Because Excel is a "murky"
product :-(.


----- original message -----