formula help

E

excel303

I want to be able to add a column with mixed numerals. For example: Th
sum
of A2:A34 with the values in the cells being 1B or 1F or so. :confused
What would the formula be
 
C

CLR

ASAP Utilities, a free add-in available at www.asap-utilities.com has a
feature that will remove all alpha-characters from cells, leaving only the
"number" parts.......

hth
Vaya con Dios,
Chuck, CABGx3
 
E

excel303

That works, but, I need to keep the values in the cells, only add th
numbers. ASAP Utilities is awesome by the way
 
C

CLR

Copy your data over to a helper column and run the routine on that
column.....this will keep your original data intact........

Vaya con Dios,
Chuck, CABGx3
 
E

excel303

I'm using the following formula with a helper column, now I'm not able
to add the helper column. :confused: :confused:


=LEFT(MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),MAX(SEARCH({0,1,2,3,4,5,6,7,8,9},"0123456789"&A1))),SUMPRODUCT(--ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&(LEN(A1)))),1))))
 
C

CLR

I don't understand why you are using the formula.........I thought you just
had mixed text and numerical values in column A and wanted to just add the
numerical parts...............be that the case, then just copy and
paste-special-values your column A over to a helper column and then use ASAP
Utilities to delete all the alpha-characters in that helper column then add
the numbers that are left.....

Vaya con Dios,
Chuck, CABGx3
 
E

excel303

I apologize for the confusion, I'm creating a worksheet for other user
to input. I have a column with mixed numerals: ie 1b or 1f o
2b....Ijust need to add the numbers automaticaly, but still keep th
letters present.
Thank you so much for your help;)
 
C

CLR

Are you wanting to "count" the 1b's and the 1f's and the 2b's, etc?

Then try
=COUNTIF(A:A,"1b")
=COUNTIF(A:A,"1f")
=COUNTIF(A:A,"2b")

If you are wanting to sum the 1's and 2's, then try my suggestion in my last
post.

Post back if you're still having trouble.......maybe give some specific cell
values and what results you expect.

Vaya con Dios,
Chuck, CABGx3
 
E

excel303

Ok, my spreadsheet is used to track different types of information, I'
good with the rest of it. The one column I need help with is fro
U4:U27. in this column the user will input a letter(F or B) followed b
the quantity of each. Is there a way to sum the numbers only and stil
have the original value showing in the cell the user typed in?

1F
1b
Total:
 
C

CLR

If you want to use a helper column, then you can change each value into a
number and sum that column using this...copied down from U4 to U27

=IF(LEN(U4)>0,MID(U4,2,99)*1,"")

The only way I know to sum them without using a helper column would be a
humongus formula, like this......

=SUM(MID(U4,2,99*1),MID(U5,2,99)*1,MID(U6,2,99)*1.....etc etc to U27)

hth
Vaya con Dios,
Chuck, CABGx3
 
Top