Text in SUMPRODUCT
 

Hello,

I can't figure out why the following keeps happening. I have a list of
data with the date, type of machine, chargable hours, and non-
chargable hours. Why is it that I can do this:

=SUMPRODUCT(('2007-2008 Breakdown'!$C$2:$C
$2255="Crusher")*(MONTH('2007-2008 Breakdown'!$A$2:$A
$2255)=11)*('2007-2008 Breakdown'!$E$2:$E$2255))

With the word "Crusher" in this formula, it returns the right number.
If I write the word "Crusher" in the cell beside this one and
reference it as the cell value:

=SUMPRODUCT(('2007-2008 Breakdown'!$C$2:$C
$2255="A4")*(MONTH('2007-2008 Breakdown'!$A$2:$A$2255)=11)*('2007-2008
Breakdown'!$E$2:$E$2255))

It returns zero. Is there any way to use the cells instead of typing
each word. Thanks in advance.

John

Text in SUMPRODUCT
 

Yes, you can - you just don't need the quotes around the cell
reference. Try it like this:

=SUMPRODUCT(('2007-2008 Breakdown'!$C$2:$C$2255=A4)*(MONTH('2007-2008
Breakdown'!$A$2:$A$2255)=11)*('2007-2008 Breakdown'!$E$2:$E$2255))

Hope this helps.

Pete

On Mar 11, 7:28*pm, "J. Trucking" wrote:
Hello,

I can't figure out why the following keeps happening. I have a list of
data with the date, type of machine, chargable hours, and non-
chargable hours. *Why is it that I can do this:

=SUMPRODUCT(('2007-2008 Breakdown'!$C$2:$C
$2255="Crusher")*(MONTH('2007-2008 Breakdown'!$A$2:$A
$2255)=11)*('2007-2008 Breakdown'!$E$2:$E$2255))

With the word "Crusher" in this formula, it returns the right number.
If I write the word "Crusher" in the cell beside this one and
reference it as the cell value:

=SUMPRODUCT(('2007-2008 Breakdown'!$C$2:$C
$2255="A4")*(MONTH('2007-2008 Breakdown'!$A$2:$A$2255)=11)*('2007-2008
Breakdown'!$E$2:$E$2255))

It returns zero. *Is there any way to use the cells instead of typing
each word. *Thanks in advance.

John

Text in SUMPRODUCT
 

Hi,

Putting your cell reference A4 in quotes make it look for the value A4 and
not the value IN A4. Try this

=SUMPRODUCT(('2007-2008 Breakdown'!$C$2:$C
$2255=A4)*(MONTH('2007-2008 Breakdown'!$A$2:$A$2255)=11)*('2007-2008
Breakdown'!$E$2:$E$2255))


"J. Trucking" wrote:

Hello,

I can't figure out why the following keeps happening. I have a list of
data with the date, type of machine, chargable hours, and non-
chargable hours. Why is it that I can do this:

=SUMPRODUCT(('2007-2008 Breakdown'!$C$2:$C
$2255="Crusher")*(MONTH('2007-2008 Breakdown'!$A$2:$A
$2255)=11)*('2007-2008 Breakdown'!$E$2:$E$2255))

With the word "Crusher" in this formula, it returns the right number.
If I write the word "Crusher" in the cell beside this one and
reference it as the cell value:

=SUMPRODUCT(('2007-2008 Breakdown'!$C$2:$C
$2255="A4")*(MONTH('2007-2008 Breakdown'!$A$2:$A$2255)=11)*('2007-2008
Breakdown'!$E$2:$E$2255))

It returns zero. Is there any way to use the cells instead of typing
each word. Thanks in advance.

John

Text in SUMPRODUCT
 

Your modified formula, changing "Crusher" to "A4" is doing just that. It is
comparing the values to "A4". "A4" is a text literal. If you want to
compare to the content of cell A4, simply use A4.

Tyro

"J. Trucking" wrote in message
...
Hello,

I can't figure out why the following keeps happening. I have a list of
data with the date, type of machine, chargable hours, and non-
chargable hours. Why is it that I can do this:

=SUMPRODUCT(('2007-2008 Breakdown'!$C$2:$C
$2255="Crusher")*(MONTH('2007-2008 Breakdown'!$A$2:$A
$2255)=11)*('2007-2008 Breakdown'!$E$2:$E$2255))

With the word "Crusher" in this formula, it returns the right number.
If I write the word "Crusher" in the cell beside this one and
reference it as the cell value:

=SUMPRODUCT(('2007-2008 Breakdown'!$C$2:$C
$2255="A4")*(MONTH('2007-2008 Breakdown'!$A$2:$A$2255)=11)*('2007-2008
Breakdown'!$E$2:$E$2255))

It returns zero. Is there any way to use the cells instead of typing
each word. Thanks in advance.

John

Text in SUMPRODUCT
 

Tyro,

do you not check if a post has been responded to? This one had two
responses, both about 3 hours before yours, and both making the same point
that you do.

Pete

"Tyro" wrote in message
. net...
Your modified formula, changing "Crusher" to "A4" is doing just that. It
is comparing the values to "A4". "A4" is a text literal. If you want to
compare to the content of cell A4, simply use A4.

Tyro

"J. Trucking" wrote in message
...
Hello,

I can't figure out why the following keeps happening. I have a list of
data with the date, type of machine, chargable hours, and non-
chargable hours. Why is it that I can do this:

=SUMPRODUCT(('2007-2008 Breakdown'!$C$2:$C
$2255="Crusher")*(MONTH('2007-2008 Breakdown'!$A$2:$A
$2255)=11)*('2007-2008 Breakdown'!$E$2:$E$2255))

With the word "Crusher" in this formula, it returns the right number.
If I write the word "Crusher" in the cell beside this one and
reference it as the cell value:

=SUMPRODUCT(('2007-2008 Breakdown'!$C$2:$C
$2255="A4")*(MONTH('2007-2008 Breakdown'!$A$2:$A$2255)=11)*('2007-2008
Breakdown'!$E$2:$E$2255))

It returns zero. Is there any way to use the cells instead of typing
each word. Thanks in advance.

John

Text in SUMPRODUCT
 

Got news for you. I responded only once.

Tyro

"Pete_UK" wrote in message
...
Tyro,

do you not check if a post has been responded to? This one had two
responses, both about 3 hours before yours, and both making the same point
that you do.

Pete

"Tyro" wrote in message
. net...
Your modified formula, changing "Crusher" to "A4" is doing just that. It
is comparing the values to "A4". "A4" is a text literal. If you want to
compare to the content of cell A4, simply use A4.

Tyro

"J. Trucking" wrote in message
...
Hello,

I can't figure out why the following keeps happening. I have a list of
data with the date, type of machine, chargable hours, and non-
chargable hours. Why is it that I can do this:

=SUMPRODUCT(('2007-2008 Breakdown'!$C$2:$C
$2255="Crusher")*(MONTH('2007-2008 Breakdown'!$A$2:$A
$2255)=11)*('2007-2008 Breakdown'!$E$2:$E$2255))

With the word "Crusher" in this formula, it returns the right number.
If I write the word "Crusher" in the cell beside this one and
reference it as the cell value:

=SUMPRODUCT(('2007-2008 Breakdown'!$C$2:$C
$2255="A4")*(MONTH('2007-2008 Breakdown'!$A$2:$A$2255)=11)*('2007-2008
Breakdown'!$E$2:$E$2255))

It returns zero. Is there any way to use the cells instead of typing
each word. Thanks in advance.

John