Sumproduct with text values

  • Thread starter Thread starter Chad
  • Start date Start date
C

Chad

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.
 
You can only use the whole column in xl2007.

What version of excel are you using?
 
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
 
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.
 
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.
 
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)
 
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.
 
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.
 
Back
Top