How do I sum cells and ignore text

bucci35

Active Member
Joined
Jul 6, 2002
Messages
341
Office Version
  1. 365
Platform
  1. Windows
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.

Any ideas?
 
A1 = 10 Runs
A2 = 10
A3 = NO RUN
A4 = -15 Points

What will be the sum of A1:A4 , my answer would be 5
Please suggest a formula which will extract 10 from " 10 Runs " , No Run as 0 Value , and -15

Thanks & Regards

Salman
 
Upvote 0

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
I used the formula below in a schedule I am creating. The schedule reads (Time Out - Time In) - (Lunch out - Lunch in) to give me a total number hours worked for each employee. The schedule is on one tab and the data is on another. I was originally getting the ### error if the cells were blank (person not working that day). When I sum all the hours worked for that person, that month, It looked like the sum was correct, I was getting a number close but not correct. It was deducting 30 minutes from each cell that had the error. I used the formula below which corrected the ### error and sums correctly however, it does not subtract the lunch break. My current formula before attempting the one below is. =(DECEMBER!N8-DECEMBER!M8)-(DECEMBER!$AB$7-DECEMBER!$AA$7). I need to know how to adapt the new formula =IF(AND(ISNUMBER(DECEMBER!K6),ISNUMBER(DECEMBER!J6)),DECEMBER!K6-DECEMBER!J6,"") to also subtract the lunch break
___________________________________________________
Use a formula like

=IF(AND(ISNUMBER(F1),ISNUMBER(G1)),F1*G1,"")

which will leave the result cell blank if either of the values that you are trying to multiply is non-numeric.
 
Upvote 0
I want to use a formula to Sum Cells Containing Text And Numbers Ignoring The Text text in all columns like =SumNumbers(A1:G1). can you help?
 
Upvote 0

Forum statistics

Threads
1,213,568
Messages
6,114,348
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top