Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct Question
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct Question
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. -- Gary''s Student - gsnu201001 "JCS" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct Question
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" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct Question
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 "Huber57" wrote: 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" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct Question
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 "JCS" wrote: 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 "Huber57" wrote: 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" wrote: 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct Question
"JCS" wrote:
=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" wrote: 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct Question
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" wrote: "JCS" wrote: =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" wrote: 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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct Question
"JCS" wrote:
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 ----- "JCS" wrote in message ... 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" wrote: "JCS" wrote: =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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct Question | Excel Discussion (Misc queries) | |||
Sumproduct question? | Excel Discussion (Misc queries) | |||
SUMPRODUCT question | Excel Worksheet Functions | |||
SUMPRODUCT question | Excel Worksheet Functions | |||
Sumproduct question | Excel Worksheet Functions |