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.

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

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.

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.

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.

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.

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.

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.

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