Tracker
HOME    TRACKER    Excel

Sum Cells With Numbers & Text

I have cells with '1 hour' , '5 hours' , '2.5 hours'. All I want to do is add the numbers and ignore the text.

Related Forum Messages:
Sum A Range That Some Cells Have Text And Numbers
How can I sum all the values in a range of cells where the cells contain either a number or text and a number. Please note the following example?

C17=9
D17=4
E17=8TD
F17=3TP
G17=7

I want the total of 31 but excel will ignore the cells with text.

Conditional Formula: If Text, Repeat As Number To Sum, Else Sum Numbers.
I can get only so far, then stumped:3 columns (Hours, Rate, Amount). "Hours" is a 'List' with data from from another sheet. Data is named 'Worked'. 'Worked' is all 2 decimal numeric (represents total time worked), except first item called "Live In"
IF 'Hours' is "Live In", I can use: =IF(A1="Live In", SUM(B1*1))

How can I make it conditional so that if it's not "Live In", then it will SUM(A1*B1)?. SideNote: 'Worked' LIST has total time with minutes expressed as 1/4 of hour (i.e., 1.25 = 1 hour, 15 minutes).

Sum Numbers Within Text
Note: I have uploaded a sample spreadsheet with the problem and the required results. I have text (eg. 1-* to 15-*) (* is a wild card) being entered in the following columns

Q21:Q128
R21:R128
V21:V128
W21:W128
AA21:AA128
AB21:AB128
AF21:AF128
AG21:AG128
AK21:AK128
AL21:AL128.....

The starting row for each above column is 21 and ends at 128. The text is different in every cell where text is entered, some cells will be blank. Each number (1-* to 15-*) appears only once on the spreadsheet are moved around. On the sample spreadsheet I have created a summary area in the left corner of the spreadsheet and this is where I would like the results to appear. I am using the following formula. The only issue is I need it work from 1-* to 15-*. Right now it only works up to 9-*. An example is 1-1 and the result is in cell D9 on the sample spreadsheet. = SUMPRODUCT((LEFT(Q21:BK128,2)="9-")*(MOD(COLUMN(Q21:BK128)-COLUMN(Q21),5)<2))

Sum Text Formatted Numbers
Is it possible to sum cells that contain numbers converted to text? I used TEXT function to convert them because I needed to format them (to display three decimal digits, if number < than 0.05, three digits otherwise). But now SUM Formula doesn't work.

Match Text To Numbers And Sum
In column 1 are 220,000 words (that's a lot ), every words corresponds to a number between 1 and 13 in column 2. Now i want to recode texts (column 2) using the codes in columns 1 and 2. I could insert all text in one cell, or i could insert each word in one cell. Then I want to calculate the sum of the corresponding values (column 2) of all the words (column 1) for each time they occur in the text (column 3). In other words, i want to recode text to numbers and i want to calculate their sum.

Sum Cell Numbers Which Are Preceded With Text
I have a number of cells which contain information such as 'H8', 'S4' and 'T6', etc, etc.

What I want is a formula which sums just the numerical part of these cells, so would add up the 8 plus 6 plus 4.

I have hundereds of these cells filled out like this. The first character in the cell is always a letter and then the number will either be a singe number (e.g. H8) or decimal (e.g. H7.5).

SUM Numbers & Text In Same Cell
I have several cells I need to sum that are MB K size related. IE.....

Circuits per Router....
155 MEG
85 MEG
100 MEG
768 K
512 K
76 MEG

need to total these
add up the MEG as 416MEG
and the K 1.2MEG
Total 417.2MEG

Sum Cells Only If 1 Or More Contain Numbers
I have a Sum formula adding three cells placed every other column (e.g. a1,c1,e1) these cells also contain formulae which links them to cells on another sheet.

My problem is where I try to sum these three cells the formula returns zero when all three are blank. I do understand I can use the If function or the options function to hide zeros but there maybe occasions when the sum might actually result in a zero.

The cell needs to return blank when the other cells are blank as it changes yet another cell which is instructed to change colour according to its result, and 0 when no data is entered would give the wrong impression to other users.

Stripping Text From Urls With Numbers/text/hyphens Without Effecting Cells.
I generated my urls to online photos, I referenced cells where some are only numerical (ex. 479) while others contain a numerical/text mix (ex. 3014-RACK). Here is my url code in excel...

Count / Sum Numbers In Column Where Specific Text Exists In Adjacent Cell
One has numbers the other has "YES" or is blank. I want a formula to look at the second column and where there is a "Yes" then count the number in the same row in the first column.

Count Number Of Rows That Have Numbers And Divide Sum Of Cells
I have a 52 (one for each week) page workbook. I am trying to average cell J9 for the entire 52 pages. However the information is not added to the cell until the end of the week so week 30-52 all have 0s and should not be counted in the dividing number. Is there a way to have Excel count the number of cells that have a number (not 0) and divide the sum of the cells but that number.

=(WK1!J9+.....WK52!J9) / (counted number of cells not = 0)

Sum Odd Or Even Numbers & Visible Or Filtered Cells
I need to subtotal/sum a series of columns individually (one column per month across a date range) by the odd numbered visible/ filtered rows as well as (separately) by the even numbered visible/filtered rows. Preferably the totals would recalculate like a SUBTOTAL whenever the filter is altered.

I've explored using Mod and SpecialCells(xlCellTypeVisible) but I can't get a syntactically correct combination to bring back the desired results.

Sum Cells Based On Part Text In Other Cells
I want to add all of the numbers in column D if Column C contains the word "REMO"

Sum Cells And Ignore Text
I have cells A1 through A10 which can contain numbers or text. I want to be able to sum this range while always ignoring the ones with text so as to eliminate the error when summing.

Remove Text From Cells Which Should Have Only Numbers
way to remove text from cells that should only contain numbers.
Example: 124 fcm 12 std

Remove Numbers From Cells With Text
I have copied information from an outside source to Excel. Unfortunately, the information includes numbers preceding the text which I need to delete for all records. Is there an easier way of deleting this information without going to each individual cell to delete the numbers

Removing Numbers From Cells With Text
I have a bunch of cells that have City and Zip Code combinations.

Ex: Chicago 606
Fayettville 72701
SACRAMENTO 95691

Some of them are 3 digit zips and others are 5 digits. I just want to weed out anything with a number leaving the city names.

SumIf On Cells With Text & Numbers
I have the data below, there is alot more but I'll just be specific and i'll expand it myself to include the rest that I need.

I want to do sumif if in Column A that is between range R10 and R99, but I cannot figure out how to get the formula to work right since I have text instead of numbers. Also some of the other sumif's i need to do have only letters (for ex. RAA to RAZ or RDA to RDZ).

...
R1021
R1417
R1519
R187
R20131
R249
R2514
R2811
...

Delete All Numbers Like: #####-## From Text Strings In Cells
I need to remove multiple instances of pages numbers from a 2000 character text string I have dumped into a cell. The spacing is not equal between the page numbers but they always take the form |#####-##| i.e. bar delimited 5 digit-2 digit. I tried SUBSTITUTE but it will not accept wildcards. I was thinking of looping through a SEARCH. The numbers are ordered ie |00001-01| to |00001-25| then |00002-01| etc, stopping at 25. This might lend to looping but I couldn't wrap my head around the VBA to accomplish that. A sample of the text

"|00006-01| (Defendants' Exhibit Nos. 1,2 marked for |00006-02| Identification.) |00006-03| BY MR. JOHNSON: |00006-04| Q. Doctor, I am handing you Exhibits 1 and 2. |00006-05| Exhibit 2, is that the one that you brought with you, |00006-06| the deposition notice of today? |00006-07| A. I believe so, yes." Auto Merged Post Until 24 Hrs Passes;I should have said this before but this is for use on Excel 2003 WinXP.

Delete Text In Cells Leaving Only Numbers
i m creating a macro to delete extra characters in a column. I have over 200,000.00 records in one column which consists of numbers with characters. For example:

#80723666
-80726960
80730187--
/ 80730279
80736277 /
( )80739210
* 80739823
& 80735380
80796440 @
## 80722138

if anybody can come up with a macro, so that I can just have the numbers and nothing else. I would like to exclude the following -~!@#\$%^&*()_=+?/.";:|][{},^` within a cell.

Remove Text Character From Cells With Numbers
I want to change the character ~ with . in order to be able to make them numeric values to be feeded to other functions. But REPLACE seems not doing the job so I've been checking out other options such as seperating after and before the character ~. Details are below. I've been trying to use this formula to extract values from a delimited database which I open with excel. The formula that has brought me close is =IF(ISNUMBER(E51)=FALSE,LEFT(E51,LEN(E51)- FIND("~",E51)),E51)

14010~000
3210~0000

When I import the database, the figures above have originals as 14010.00000 & 3210.00000 but transfer to excel as above. As far as I have observed 9 character spaces are displayed & the DOT transfers to ~ for some reason. I need the LEFT section of the ~

Find Cells Contaning Text Not Numbers
I need to find cells containing text (Alpha characters only) in a single column, an return the row(+1 then -1). The column contains Group names (Unknown) , and under each name follows numeric data until the next group name. The cells are not formatted other than BOLD names. I need to exctract the numeric data in groups.

EG.
----
NameA
324
234
65
NameB
345
67
Footer
----
etc.

I have manged using the following code, but this always assumes there is an "e" in the name, but would prefer a more foolproof method. (I used "e" cos it is ok so far, BUT this could change in the future)

Sc = "A"
Sr = "1"
With Worksheets(1).Range(Sc & Sr & ":A500").................

Sum Up A Column Of Cells Which Have Both Text With And Without Strikethrough Formatting
I'm trying to sum up a column of cells which have both text with and without strikethrough formatting. I've looked around and got some VB codes but it doesn't work for me.

I still get the annoying #NAME? error. It did work initially but after moving the file to another computer it just became #NAME? again.

Sum Time Cells In Range With Empty Text
trying to add cells together that contain time (hours and minutes). The cell containing the total formula shows the #VALUE! result when and only when one or more cells being added do not have a value in them.

The times have been entered as actual times of the day, formatted for AM/PM.

A simple adding formula works fine, but all cells must have a value in them to obtain a valid result.

I attach a sample of the sheet.

Gathering The Sum Of Cells Based On The Text In The Adjacent Cell
I have a column of dollar amounts (variances) and next to each is the shift they belong to.

I am looking to get a break down of the variances for each shift.

Attached is an example of what it looks like.

Format Formulas, Constants, Numbers & Text Cells Differently
I am attempting to write a VBA function that will add textual formatting to each cell that has content. I am just learning VBA; so far, as an example I have

Sub NewSub()
i = 1
j = 1
Do
Cells(i, j) = "'" & Range(i.j).Value & "',"
i = i + 1
Loop Until i = 40
End Sub

but, as I'm sure you'll notice, it does not work. In essence I want to wrap quotes and follow with a comma each cell that contains any content. The actual content test I haven't gotten to yet.

Gathering The Sum Of Negative Numbers & Positive Numbers In A Column
I have a column of variances, these contain both negative numbers and positive numbers. I want to gather a sum of all the negative numbers and positive numbers separtely. Basically saying all the positive overeages = this amount And all the negative shortages = this amount. you can see the attached sample.

Sum In VBA: Sum The Numbers If The Match Occurs In A & K
With Range("H2:H5000")
.FormulaR1C1 = "= SUMPRODUCT(('Data Sheet'!R2C11:R5000C11=RC1)*('Data Sheet'!R2C81:R5000C81=""G - Prior policy incident handling errors""))"
.Copy
.PasteSpecial Paste:=xlPasteValues
End With

Right now its simply counting, but what I need it to do is to sum col L if col A matches col K. Col L contains numbers, and I need a sum of those numbers if the match occurs in A & K.

Sum Formula: Sum Of The Numbers In Column
I would like a formula for cells B1, B2, & B3 that will give me the sum of the numbers in Column B which are next to their corresponding plan in Column A. The attachment is a sample of a sheet that will be having approx 500 rows to work with. I have tried using the "Sumif" funtion, but I can't seem to get it to work correctly.

Sum Of Numbers Excluding Negative Numbers
How can I get the sum of a range excluding any negative numbers?

I've thought about using a new column and using if statement to separate but it would be easier if i could use the same column.

Protect Cells So That Users Can Only Input Numbers Or The Text "NA"
I need help with a macro that allows users to put only numbers from a range of 0 to 100 and the only text allowed must be "NA" in an Excel sheet. I know that I can protect the cells from being erased and all but my main issue is that my users are putting all kinds of text values instead of NA or numbers above 100 making my life harder than it is.

Extracting Numbers From Text And Turning The Numbers Into Rows Of Data
The format of the text in which I need to extract numbers is as follows:

23411268 - 23411270

Need to extract the following:

23411268
23411269
23411270

These numbers have to be listed in three seperate rows.

Convert Numbers Stored As Text To Numbers (attached File)
In the ID column of the attached excel file whenever I convert the numbers stored as text to numbers it results in a weird transformation.

e.g. an ID of 480610141001 becomes 4.8061E+11. When I do the same process in the name column, which has similar numbers, it will give the correct result following the same process.

See attached file. A colleague is downloading rows of data from a website which contains a number field Excel is currently treating as Text after being pasted in. My spreadsheet includes just a sample of the many rows of data however as you can see the VALUE function refuses to convert these text values to numbers. How these might be converted and why the VALUE function refuses to work in this case?

Compare 2 Columns For Numbers In Mixed Text & Numbers
I need to compare two colums by number decription for example m344 in one column and fsh344-1 in another. All I want to match is 344. In column a I want to indcate the match by placing an X by each match. View my attachment for reference. I don't know if it makes a difference but the columns are centered in my original spreadsheet.

Converting Decimal Numbers To Text With Dot Numbers
we work with both Lotus 123 and Excel 2003. Lotus will be gone next year, but for now, the official mean to publish our reports is Lotus. With my work, I copy/paste a Lotus page to Excel. I use the following macro to convert Lotus format numbers (which Excel considers as text) to real numbers:

Sub ForceToNumber()
Dim wSheet As Worksheet
For Each wSheet In Worksheets
With wSheet
. Range("IV65536") = vbNullString
.Range("IV65536").Copy
End With
Next wSheet
End Sub

Source : http://www.ozgrid.com/forum/showthre...087#post184087. The problem is that I need to send back this data in Lotus. Excel considers decimal numbers with a coma as real numbers and numbers with a dot as a text. This previous macro fixes that. However, Lotus works the other way. Only numbers with a dot are considered real numbers. So I would need to find a way to code a macro that converts any numbers in the Excel sheet to a number with a dot. It's a bit like doing the opposite operation.

Converting Numbers Stored As Text To Numbers Via Macro
I'm looking for the VBA command for this function. I tried just recording a macro in where I perform the task but it didn't record anything. Tried it several times even.

Convert Numbers As Text To True Numbers
I have the following two macros that I use daily:

Dim c As Range
For Each c In Selection
If Not IsError(c.Value) Then
If Left(c.Value, 1) <> "'" And c.Value <> "" Then
If InStr(c.Value, "E+") > 0 Then
c.Value = "'" & c.Formula
Else

I use these because I frequently copy billing account numbers that have leading zeros from some proprietary software and work with them in Excel. I need the leading zeros to facilitate VLookUp's. When I paste the data into Excel it truncates the leading zeros. My typical procedure is:

1. Copy a column of values from our proprietary software
2. Paste it in Excel (Ctrl+V)
3. While newly pasted data is still selected I hit Ctrl+1 which brings up the Format Cells form.
4. I double click "Text"
5. I re-paste into Excel (Ctrl+V)
6. I run my macro to add tic marks to the values so the leading zeros never get lost.

The problem occurs when I copy multiple columns of data and paste it in Excel. If one of the columns of data contains numbers or currency then I select that range of values and run the macro to get rid of the tic marks. After running the macro the numbers are still treated as text. When I select a range of those values and right-click on the status bar and select " Sum" there is no value. No big deal, I just highlight them and format the cells as numbers or currency. But after formatting them as numbers or currency, they retain their format as text until I click in the formula bar for each individual cell and hit enter, at which time they take on the number or currency format I have chosen and the sum can then be displayed in the status bar.

How can I force the formatting I chose to take effect on the data immediately without me having to individually "fake change" the data in each cell?

Convert Numbers Seen As Text To True Numbers
I have text as numbers downloaded to excel sheets from crystal that i need to add together on a summary sheet. I've tried using Value, TRIM and CLEAN functions to no avail. the TYPE function is reading the cell as Text and I have also tried multiplying by 1. Does anybody know how I can get around this ? hopefully I've attached an extract of the file

Match (text) Numbers To Numbers From One Cell To Another
I need the " solutions cells " to match to the " source cells " and return a " yes " or " no ". in cells d4:d10. Ex: #1 cell C4=01234598, it will conduct a search & match of cell A5 which contains the number 123, since cell C4 contains all 3 numbers from cell A5, then the formula would result in a " Yes ". The match has to contain all 3 numbers from the solution cells to the source cells.

Ex: #2 cell C7=67891234, it will conduct a search & match of cell A8 =901. Since cell C7 only contains the 9&1 from cell A8, it would return a " No " because cell C7 did not contain all 3 numbers from cell A8. I'm not sure if the IF, Match, Index or Count formula is the right one to use, they all seem to partially work, but still don't complete this problem.

Convert Numbers, Seen As Text, To True Numbers
The back story is that from a system we use, when you download data from inside it, Excel treats most of the cells with a "Number Stored As Text" error. I had problems with this and it was just generally annoying so I wrote the below macro that converts each of the cells back to general cells and reinputs the data, essentially reseting the cell and keeping the data. Being that this is a loop, it takes a good while to run if alot of data was downloaded. Any idea's on how to speed it up besides screen updating? Note, other stuff happens in the first macro, but it is just cosmetic non-intensive things. It calls the second macro because I wanted them seperate should I want to just run the second.

Sub CleanData()
Application. ScreenUpdating = False
' Find the Right most cell and the Bottom most cell and
' run the macro from A1 to this cell recursively
Range("IV1").End(xlToLeft).Select
Col = Selection.Column
Range("A65536").End(xlUp).Select
Row = Selection.Row
Range("A2").Select
Range("A1", Cells(Row, Col)).Select
Application.Run ("FixTextAsNumberError")
Application.ScreenUpdating = True
'
End Sub..............

Autofind & Sum (select The Respective Cells And SUM)
To fill in the ??? in attached file, I select the respective cells and SUM. Would there be a way to automate as:

For each entry in column D, Excel picks out itself the names in column-A where-ever they come, picks the corresponding values from column-B, sums them and reproduces the summation in column E.

Add Sum To End Of Range To Sum All Cells Above
I'm getting a Type Mismatch (error 13) with the following:

Range("I1").End(xlDown).Offset(1, 0) = "= Sum(" & Range("I2", Range("I2").End(xlDown)) & ")"

Basically I want to do an autosum on the first blank cell for a list of numbers in a column

Sum If, But The Sum Range Is Seperate Cells ?
I want to do a simple Sum if. My range is simple and so is my criteria, but the actual range of cells to sum is not in one continus row or column. Can I do the sumrange as seperate cells?

Sum Numbers Of Certain Value
I am trying to work out discounts based on the number of days an item was late.

So, is it possible to find all the entries which have the value of, say, 2 in column D, and add together their values from column B?

All in once cell?

I have attached a sample of the file for you to see.

Sum Numbers Less Than Zero
Assume that I have a row of numbers like this:

1 3 4 -6 -5 -1 -3 17 -5 20

I would like to get the code that would enter for me in another row these results:

1 3 4 2 5

i.e. If the range within the row is less than zero, sum it and add it to the first positive number that comes sequentially after in the row.

Using If To Sum() Non-sequential Numbers
I need a formula to add data in it's respective column, only if the month and year match.

The columns of data will be very long, thousands of lines possibly.

So, basically, in this example.

in O1, if the the data in the columns below match the month(M1) and year (N1), then sum those variables.

Sum Numbers From A Table
I have a problem I hope someone can help me with. I think it is probably easier to open the attached excel file to see what I would like to do.

I really hope someone can help me with this problem. If it is hard to understand exactly what I would like to do then tell me and I will try to make it clearer.

Sum The Last 6 Numbers Below 100
I've been trying to come up with a formula for this for a few hours now, but I can't work it out.

I'm using Excel 2002.

In column C I have a list of 50 numbers. They range from cell C1 to C50.
The numbers are either above of below 100.

Now, in cell D51 I want to sum the last 6 numbers in the list below 100.

So if the list would be:

...
C40 = 19
C41 = 25
C42 = 3
C43 =234
C44 =0
C45 =12
C46 =345
C47 = 0
C48 = 129
C49 = 1
C50 = 12

Now, the last six numbers below 100 (going upwards) are: 12, 1, 0, 12, 0, 3.
These numbers need to be summed. The outcome in this case would be 28.