| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct with text values
I cant get either of these formulas:
=SUMPRODUCT(--('Assignments Copy'!C:C="Text 1"),--('Assignments Copy'!J:J="Text 2"),--'Assignments Copy'!H:H) or =SUMPRODUCT(('Assignments Copy'!C:C="Text 1")*('Assignments Copy'!J:J="Text 2")*('Assignments Copy'!H:H)) to return the proper result. In my test example, I should get 1.00, but I get a #VALUE error. Thoughts? Any help is appreciated. Thank you. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct with text values
You can only use the whole column in xl2007.
What version of excel are you using? Chad wrote: I cant get either of these formulas: =SUMPRODUCT(--('Assignments Copy'!C:C="Text 1"),--('Assignments Copy'!J:J="Text 2"),--'Assignments Copy'!H:H) or =SUMPRODUCT(('Assignments Copy'!C:C="Text 1")*('Assignments Copy'!J:J="Text 2")*('Assignments Copy'!H:H)) to return the proper result. In my test example, I should get 1.00, but I get a #VALUE error. Thoughts? Any help is appreciated. Thank you. -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct with text values
If you using Excel 2003 then you can't use full columns for sumproduct, not
sure about 2007 so for 2003 try this =SUMPRODUCT(('Assignments Copy'!C1:C100="Text 1")*('Assignments Copy'!J1:J100="Text 2")*('Assignments Copy'!H1:H100)) or similarly modified =SUMPRODUCT(--('Assignments Copy'!C1:C100="Text 1"),--('Assignments Copy'!J1:J100="Text 2"),--'Assignments Copy'!H1:H100) Mike "Chad" wrote: I cant get either of these formulas: =SUMPRODUCT(--('Assignments Copy'!C:C="Text 1"),--('Assignments Copy'!J:J="Text 2"),--'Assignments Copy'!H:H) or =SUMPRODUCT(('Assignments Copy'!C:C="Text 1")*('Assignments Copy'!J:J="Text 2")*('Assignments Copy'!H:H)) to return the proper result. In my test example, I should get 1.00, but I get a #VALUE error. Thoughts? Any help is appreciated. Thank you. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct with text values
Didn't work. I'm using 2007, saving the file as a 97-03.
"Mike H" wrote: If you using Excel 2003 then you can't use full columns for sumproduct, not sure about 2007 so for 2003 try this =SUMPRODUCT(('Assignments Copy'!C1:C100="Text 1")*('Assignments Copy'!J1:J100="Text 2")*('Assignments Copy'!H1:H100)) or similarly modified =SUMPRODUCT(--('Assignments Copy'!C1:C100="Text 1"),--('Assignments Copy'!J1:J100="Text 2"),--'Assignments Copy'!H1:H100) Mike "Chad" wrote: I cant get either of these formulas: =SUMPRODUCT(--('Assignments Copy'!C:C="Text 1"),--('Assignments Copy'!J:J="Text 2"),--'Assignments Copy'!H:H) or =SUMPRODUCT(('Assignments Copy'!C:C="Text 1")*('Assignments Copy'!J:J="Text 2")*('Assignments Copy'!H:H)) to return the proper result. In my test example, I should get 1.00, but I get a #VALUE error. Thoughts? Any help is appreciated. Thank you. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct with text values
Hi,
If you are using 2003 or earlier you can't refer to the entire column with this formula. Change your references to J1:J65000 for example. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Chad" wrote: I cant get either of these formulas: =SUMPRODUCT(--('Assignments Copy'!C:C="Text 1"),--('Assignments Copy'!J:J="Text 2"),--'Assignments Copy'!H:H) or =SUMPRODUCT(('Assignments Copy'!C:C="Text 1")*('Assignments Copy'!J:J="Text 2")*('Assignments Copy'!H:H)) to return the proper result. In my test example, I should get 1.00, but I get a #VALUE error. Thoughts? Any help is appreciated. Thank you. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct with text values
Didn't work. I'm using 2007, saving the file as a 97-03.
Formula changed to: =SUMPRODUCT(--('Assignments Copy'!C2:C500="Text 1"),--('Assignments Copy'!J2:J500="Text 2"),--'Assignments Copy'!H2:H500) "Shane Devenshire" wrote: Hi, If you are using 2003 or earlier you can't refer to the entire column with this formula. Change your references to J1:J65000 for example. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Chad" wrote: I cant get either of these formulas: =SUMPRODUCT(--('Assignments Copy'!C:C="Text 1"),--('Assignments Copy'!J:J="Text 2"),--'Assignments Copy'!H:H) or =SUMPRODUCT(('Assignments Copy'!C:C="Text 1")*('Assignments Copy'!J:J="Text 2")*('Assignments Copy'!H:H)) to return the proper result. In my test example, I should get 1.00, but I get a #VALUE error. Thoughts? Any help is appreciated. Thank you. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct with text values
Hi,
In both 2003 and 2007 these formulas will return errorrs if there are any non-numeric entries in column H. Since you haven't shown us what your data looks like or what you are trying to do we can't propose a solution. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Chad" wrote: I cant get either of these formulas: =SUMPRODUCT(--('Assignments Copy'!C:C="Text 1"),--('Assignments Copy'!J:J="Text 2"),--'Assignments Copy'!H:H) or =SUMPRODUCT(('Assignments Copy'!C:C="Text 1")*('Assignments Copy'!J:J="Text 2")*('Assignments Copy'!H:H)) to return the proper result. In my test example, I should get 1.00, but I get a #VALUE error. Thoughts? Any help is appreciated. Thank you. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct with text values
There's the problem. No numeric value in column H. Changed references to
start in row 3 to eliminate a cell with text, and like magic, there it is. Thank you very much. "Shane Devenshire" wrote: Hi, In both 2003 and 2007 these formulas will return errorrs if there are any non-numeric entries in column H. Since you haven't shown us what your data looks like or what you are trying to do we can't propose a solution. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Chad" wrote: I cant get either of these formulas: =SUMPRODUCT(--('Assignments Copy'!C:C="Text 1"),--('Assignments Copy'!J:J="Text 2"),--'Assignments Copy'!H:H) or =SUMPRODUCT(('Assignments Copy'!C:C="Text 1")*('Assignments Copy'!J:J="Text 2")*('Assignments Copy'!H:H)) to return the proper result. In my test example, I should get 1.00, but I get a #VALUE error. Thoughts? Any help is appreciated. Thank you. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct with text values
Or just change the original formula:
=SUMPRODUCT(--('Assignments Copy'!C:C="Text 1"), --('Assignments Copy'!J:J="Text 2"), 'Assignments Copy'!H:H) The -- stuff in front of the last argument tried to change the non-text to a number. But it couldn't do that, so you got that #value error. Without the -- in front of the last argument, =sumproduct() will ignore the text in column H. Chad wrote: There's the problem. No numeric value in column H. Changed references to start in row 3 to eliminate a cell with text, and like magic, there it is. Thank you very much. "Shane Devenshire" wrote: Hi, In both 2003 and 2007 these formulas will return errorrs if there are any non-numeric entries in column H. Since you haven't shown us what your data looks like or what you are trying to do we can't propose a solution. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Chad" wrote: I cant get either of these formulas: =SUMPRODUCT(--('Assignments Copy'!C:C="Text 1"),--('Assignments Copy'!J:J="Text 2"),--'Assignments Copy'!H:H) or =SUMPRODUCT(('Assignments Copy'!C:C="Text 1")*('Assignments Copy'!J:J="Text 2")*('Assignments Copy'!H:H)) to return the proper result. In my test example, I should get 1.00, but I get a #VALUE error. Thoughts? Any help is appreciated. Thank you. -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Can Sumproduct return Text values | Excel Worksheet Functions | |||
| Two Matches, then Sumproduct of Values | Excel Worksheet Functions | |||
| 2 criteria lookup of text. Return text form column 3. SUMPRODUCT t | Excel Worksheet Functions | |||
| SUMPRODUCT rounding up values | Excel Worksheet Functions | |||
| Obtaining Max and Min Values with Sumproduct | Excel Discussion (Misc queries) | |||
Hybrid Mode
