# IsNumber?

#### karlruff

##### Board Regular
Hello,
Could anyone recommend a formula for checking if the contents of each of the cells in a range contains a number, and if so, return the sum of those numbers?
Thanks
b

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

=SUM(range)

(ie SUM(A1:D10))

#### karlruff

##### Board Regular
I should have been more clear. If the formula finds the contents of any cell in the range is NOT a number, I need the formula to return an "N/A".
Thx
b

#### DonkeyOte

##### MrExcel MVP
Does this work for you (as an array)

=(ISTEXT(Range)=FALSE)*(SUM(Range))

Commit using SHIFT + CTRL + ENTER

Should see {=(ISTEXT....)}

Change range refs to A1:A10 or whatever range you need - would return 0 rather than error.

----------------------------

EDIT:

SORRY... this will work for you but will return DIV/0 as error rather than 0

=SUM(Range)/(ISTEXT(Range)=FALSE)

Again, commit as array

Last edited:

#### Scott Huish

##### MrExcel MVP

Are you not wanting to sum if any of them are blank or if any of them contain text?

If text, perhaps:

=IF(COUNTIF(A1:A10,"?*"),"N/A",SUM(A1:A10))

or if either, perhaps:

=IF(COUNT(A1:A10)=ROWS(A1:A10),SUM(A1:A10),"N/A")

Last edited:

#### karlruff

##### Board Regular
Thanks Hotpepper- I think that'll do the trick.

#### DonkeyOte

##### MrExcel MVP
The formula I gave was designed multi dimensional range.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,626
Messages
5,838,431
Members
430,547
Latest member
jopshio

### 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?

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