Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have an Excel table with some cells that are numbers and some cells that are strings. I want a function that can add search through the table, and if the cell is a number, add it(+) and if it's a string, ignore it.

I know how to do this easily in say C# but how do I do this in Excel?

This is what I want the equivelant of:

object[] celldata = new object[] {"blabla",2,3,"blabla",3,"blabla"}

int additioncell;

foreach(object cells in celldata)
    additionalcell += (int) cells;

How do I convert this into an Excel function?

share|improve this question
up vote 4 down vote accepted

The sum() function should work. It will ignore the strings.

Additionally, you can use isnumber() and istext() to check if a cell is a number or text.

share|improve this answer

Right click on the cells in question and create a custom format.enter image description here

share|improve this answer
Keep in mind that formatting this way, in general, may affect cell values in VBA. If I were to make a custom type like "derp"@, then my Range(Address).Value would look at the unformatted value. To get the formatted value, you would use Range(Address).Text. – Zairja Aug 2 '12 at 19:26

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.