# Excel - Sumproduct with text values

Asked By cha on 02-Mar-09 10:07 AM

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** replied on 02-Mar-09 10:29 AM

You can only use the whole column in xl2007.
What version of excel are you using?
--
Dave Peterson

**Mike** replied on 02-Mar-09 10:34 AM

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

**shanedevenshir** replied on 02-Mar-09 10:36 AM

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

**shanedevenshir** replied on 02-Mar-09 10:40 AM

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

**cha** replied on 02-Mar-09 10:40 AM

Didn't work. I am using 2007, saving the file as a 97-03.

**cha** replied on 02-Mar-09 10:42 AM

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)

**cha** replied on 02-Mar-09 10:47 AM

There is 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.

**Dave Peterson** replied on 02-Mar-09 11:06 AM

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.
--
Dave Peterson