Text in SUMPRODUCT

  • Thread starter Thread starter J. Trucking
  • Start date Start date
J

J. Trucking

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
 
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
 
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
 
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
 
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
 
Got news for you. I responded only once.

Tyro

Pete_UK said:
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
Click to expand...
 
Back
Top