| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| sumproduct text if | Excel Worksheet Functions | |||
| maybe by sumproduct or some other way with text. | Excel Worksheet Functions | |||
| SUMPRODUCT but with text containing | Excel Worksheet Functions | |||
| Sumproduct Text | Excel Discussion (Misc queries) | |||
| SUMPRODUCT vs Text??? | Excel Worksheet Functions | |||
Linear Mode
