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!