formula help

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

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



"excel303" wrote:

>
> I want to be able to add a column with mixed numerals. For example: The
> sum
> of A2:A34 with the values in the cells being 1B or 1F or so.
> What would the formula be?
>
>
> --
> excel303
> ------------------------------------------------------------------------
> excel303's Profile: http://www.excelforum.com/member.php...o&userid=31816
> View this thread: http://www.excelforum.com/showthread...hreadid=515440
>
>

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

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



"excel303" wrote:

>
> That works, but, I need to keep the values in the cells, only add the
> numbers. ASAP Utilities is awesome by the way.
>
>
> --
> excel303
> ------------------------------------------------------------------------
> excel303's Profile: http://www.excelforum.com/member.php...o&userid=31816
> View this thread: http://www.excelforum.com/showthread...hreadid=515440
>
>

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


=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))))

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



"excel303" wrote:

>
> I'm using the following formula with a helper column, now I'm not able
> to add the helper column.
>
>
> =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))))
>
>
> --
> excel303
> ------------------------------------------------------------------------
> excel303's Profile: http://www.excelforum.com/member.php...o&userid=31816
> View this thread: http://www.excelforum.com/showthread...hreadid=515440
>
>

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


[QUOTE=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

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


"excel303" wrote:

>
> I apologize for the confusion, I'm creating a worksheet for other users
> to input. I have a column with mixed numerals: ie 1b or 1f or
> 2b....Ijust need to add the numbers automaticaly, but still keep the
> letters present.
> Thank you so much for your help
>
>
> CLR Wrote:
> > 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
>
>
> --
> excel303
> ------------------------------------------------------------------------
> excel303's Profile: http://www.excelforum.com/member.php...o&userid=31816
> View this thread: http://www.excelforum.com/showthread...hreadid=515440
>
>

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

1F
1b
Total: 2

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



"excel303" <excel303.23oyhd_1140713405.4784@excelforum-nospam.com> wrote in
message news:excel303.23oyhd_1140713405.4784@excelforum-nospam.com...
>
> Ok, my spreadsheet is used to track different types of information, I'm
> good with the rest of it. The one column I need help with is from
> U4:U27. in this column the user will input a letter(F or B) followed by
> the quantity of each. Is there a way to sum the numbers only and still
> have the original value showing in the cell the user typed in?
>
> 1F
> 1b
> Total: 2
>
>
> --
> excel303
> ------------------------------------------------------------------------
> excel303's Profile:
http://www.excelforum.com/member.php...o&userid=31816
> View this thread: http://www.excelforum.com/showthread...hreadid=515440
>

you are awesome!!!!!!!!!

THANK YOU, THANK YOU, THANK YOU

You're very welcome, and thanks for the thanks.....

Vaya con Dios,
Chuck, CABGx3


"excel303" <excel303.23pmsa_1140744901.6702@excelforum-nospam.com> wrote in
message news:excel303.23pmsa_1140744901.6702@excelforum-nospam.com...
>
> you are awesome!!!!!!!!!
>
> THANK YOU, THANK YOU, THANK YOU
>
>
> --
> excel303
> ------------------------------------------------------------------------
> excel303's Profile:
http://www.excelforum.com/member.php...o&userid=31816
> View this thread: http://www.excelforum.com/showthread...hreadid=515440
>