Sumproduct with text values

[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.

 

[Dave Peterson]

You can only use the whole column in xl2007.

What version of excel are you using?

 

[Mike H]

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

 

[Shane Devenshire]

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.

 

[Shane Devenshire]

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.

 

[Chad]

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

 

[Chad]

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)

 

[Chad]

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.

 

[Dave Peterson]

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.