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