Thread: 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!

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!
>

Will your letters alway at the end og the cells?

Per Erik
On Wed, 26 Jan 2005 13:41:03 -0800, Carole
<Carole@discussions.microsoft.com> 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!

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 <Carole@discussions.microsoft.com>
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!

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!
> >
>

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!
>>>
>>

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!
> >>>
> >>
>

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.