Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Sum cell numbers that have certain text within the cell
I want to have a formula that I can put into cell P4, Q4, and R4 that will
sum the range "A4:D4" that contain the text "CE", "LS", and "LA". I have tried variations of sum, sumif, and sumproduct, that also included substitute, none seem to give me the answer. They either give me an error or "0". A4 = 1 CE B4 = 1.5 LS C4 = 1 LA D4 = 2.25 CE Please help! |
#2
|
|||
|
|||
For CE in P4...
=SUMPRODUCT(--(TRIM(REPLACE($A$4:$D$4,1,SEARCH(" ",$A$4:$D$4&" "),""))="CE"),--LEFT(A4:D4,SEARCH(" ",$A$4:$D$4&" "))) Carole wrote: I want to have a formula that I can put into cell P4, Q4, and R4 that will sum the range "A4:D4" that contain the text "CE", "LS", and "LA". I have tried variations of sum, sumif, and sumproduct, that also included substitute, none seem to give me the answer. They either give me an error or "0". A4 = 1 CE B4 = 1.5 LS C4 = 1 LA D4 = 2.25 CE Please help! |
#3
|
|||
|
|||
Will your letters alway at the end og the cells?
Per Erik On Wed, 26 Jan 2005 13:41:03 -0800, Carole wrote: I want to have a formula that I can put into cell P4, Q4, and R4 that will sum the range "A4:D4" that contain the text "CE", "LS", and "LA". I have tried variations of sum, sumif, and sumproduct, that also included substitute, none seem to give me the answer. They either give me an error or "0". A4 = 1 CE B4 = 1.5 LS C4 = 1 LA D4 = 2.25 CE Please help! |
#4
|
|||
|
|||
Do you mean that you are trying to store 2 pieces of information, the number 1
and the text CE, in the same cell? As evidenced by Alladin's formula, you will make your life much easier if you put the 1 in A4 and the CE in A5. Then you could use =SUMIF(A5:D5,"CE",A4:D4) On Wed, 26 Jan 2005 13:41:03 -0800, Carole wrote: I want to have a formula that I can put into cell P4, Q4, and R4 that will sum the range "A4:D4" that contain the text "CE", "LS", and "LA". I have tried variations of sum, sumif, and sumproduct, that also included substitute, none seem to give me the answer. They either give me an error or "0". A4 = 1 CE B4 = 1.5 LS C4 = 1 LA D4 = 2.25 CE Please help! |
#5
|
|||
|
|||
This didn't work. It gave me "#value!" for a result. I do need to keep the
quantity and text in the same cells that I want to add. Also, sometimes there are multiple quantities and text in the same cell but on seperate lines (1 CE "hard return" 1 LS). I need it to find and add those instances also. "Aladin Akyurek" wrote: For CE in P4... =SUMPRODUCT(--(TRIM(REPLACE($A$4:$D$4,1,SEARCH(" ",$A$4:$D$4&" "),""))="CE"),--LEFT(A4:D4,SEARCH(" ",$A$4:$D$4&" "))) Carole wrote: I want to have a formula that I can put into cell P4, Q4, and R4 that will sum the range "A4:D4" that contain the text "CE", "LS", and "LA". I have tried variations of sum, sumif, and sumproduct, that also included substitute, none seem to give me the answer. They either give me an error or "0". A4 = 1 CE B4 = 1.5 LS C4 = 1 LA D4 = 2.25 CE Please help! |
#6
|
|||
|
|||
Carole wrote:
This didn't work. Well, it should work for data you initially forwarded, consisting of a single pair of a Number followed by a Symbol. It gave me "#value!" for a result. Yes, it should do that with multiple pairs per cell. I do need to keep the quantity and text in the same cells that I want to add. Also, sometimes there are multiple quantities and text in the same cell but on seperate lines (1 CE "hard return" 1 LS). I need it to find and add those instances also. I think Myrna's observation about your data representation is now more urgent. "Aladin Akyurek" wrote: For CE in P4... =SUMPRODUCT(--(TRIM(REPLACE($A$4:$D$4,1,SEARCH(" ",$A$4:$D$4&" "),""))="CE"),--LEFT(A4:D4,SEARCH(" ",$A$4:$D$4&" "))) Carole wrote: I want to have a formula that I can put into cell P4, Q4, and R4 that will sum the range "A4:D4" that contain the text "CE", "LS", and "LA". I have tried variations of sum, sumif, and sumproduct, that also included substitute, none seem to give me the answer. They either give me an error or "0". A4 = 1 CE B4 = 1.5 LS C4 = 1 LA D4 = 2.25 CE Please help! |
#7
|
|||
|
|||
I tried limiting the formula to look at the array that either doesn't have
any input, or has only one number/text entry. It still gave me the "#value!" error. I am not able to seperate the number from the text because I have conditional formating on those entry cells so they highlight when a specific text is input in that cell. "Aladin Akyurek" wrote: Carole wrote: This didn't work. Well, it should work for data you initially forwarded, consisting of a single pair of a Number followed by a Symbol. It gave me "#value!" for a result. Yes, it should do that with multiple pairs per cell. I do need to keep the quantity and text in the same cells that I want to add. Also, sometimes there are multiple quantities and text in the same cell but on seperate lines (1 CE "hard return" 1 LS). I need it to find and add those instances also. I think Myrna's observation about your data representation is now more urgent. "Aladin Akyurek" wrote: For CE in P4... =SUMPRODUCT(--(TRIM(REPLACE($A$4:$D$4,1,SEARCH(" ",$A$4:$D$4&" "),""))="CE"),--LEFT(A4:D4,SEARCH(" ",$A$4:$D$4&" "))) Carole wrote: I want to have a formula that I can put into cell P4, Q4, and R4 that will sum the range "A4:D4" that contain the text "CE", "LS", and "LA". I have tried variations of sum, sumif, and sumproduct, that also included substitute, none seem to give me the answer. They either give me an error or "0". A4 = 1 CE B4 = 1.5 LS C4 = 1 LA D4 = 2.25 CE Please help! |
#8
|
|||
|
|||
Carole wrote:
I tried limiting the formula to look at the array that either doesn't have any input, or has only one number/text entry. It still gave me the "#value!" error. I am not able to seperate the number from the text because I have conditional formating on those entry cells so they highlight when a specific text is input in that cell. [...] The formula does indeed error out on empty cells. But it can be corrected for that... =SUMPRODUCT(--(TRIM(REPLACE($A$4:$D$4,1,SEARCH(" ",$A$4:$D$4&" "),""))="CE"),--LEFT(0&$A$4:$D$4,SEARCH(" ",$A$4:$D$4&" "))) Recall that it's meant just for pairs consisting of a number and a symbol, separated by a space. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell set to wrap text and blank line -- fix? | Excel Discussion (Misc queries) | |||
How can I write in a text in a cell using numbers and the letters. | Excel Discussion (Misc queries) | |||
how to hyperlink text to a cell | New Users to Excel | |||
Paste rows of numbers from Word into single Excel cell | Excel Discussion (Misc queries) | |||
GET.CELL | Excel Worksheet Functions |