Getting Excel to Ignore Non-Numerical Values with SUM 1

[OrangeWire]

I have a bunch of values i need to sum up. Some are non-numberic. Is there a way i can get a total of all values but not try to add the non-numberic values?

The reason i ask is because i get #VALUE! for my result.



----Extra Info----
#the coloum i would like to add up looks like this:

0
0
397
89
0
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
65
0
221
-18
-139
0
0
0
0
#VALUE! <= TOtal Value of Column

------



Thanks!

 

[mbarron]

Enter the following as an array formula. (press Crtl+shift+enter when entering not just enter)

=SUM(IF(ISNUMBER(A1:A21),F8:F28))

Mike

 

[mbarron]

Oops...
should be

=SUM(IF(ISNUMBER(A1:A21),A8:A21))



Mike

 

[DaleWatson123321]

I see no one has jumped in on this, so I'll assume for the moment that it's not easily solved with a "regular" type of solution like "SUMIF" (which I tried).

I can confirm that the "DSUM" function works.

If you're not familiar with Excel's "database formulas" like DSUM, DCOUNTA, etc, then you might have a little difficulty.

If you want to give it a try on your own, use these steps:

1) If the column of data is part of an existing "database" matrix - i.e. with unique column headings, then assign a range name to your matrix, with the top row of the range being the row with your column (or field) names. In the example formula below, I've used the name "data" (as the range name).

2) If the column of data is by itself, and doesn't have a field name, then enter any name in a cell above your data, and then highlight your data (including the field name) and assign a range name - like the name "data".

3) On a separate sheet (which you should name "Criteria"), enter the following formula. I would normally enter the formula in Column B, leaving Column A to enter a label to identify the range name you will assign to the criteria.

For example, if you enter this formula in B3...
=ISERR(colm1)=FALSE ...then enter the label "crit" in A2.

4) Assign the range name "crit" to cells B2:B3.
To do this, highlight B2:B3. Hold down <Ctrl> and hit <F3>. Type the name (crit), and <Enter>.

5) Go to the Sheet/Cell where you want the formula, and enter this formula...
=DSUM(data,1,crit)

The "1" in the formula refers to the 1st column in the range named "data". If you have a matrix of data and the field you want the formula to refer to is not the 1st column, then change the number "1" to match the location of the column.


Regards,

Dale Watson

 

[DaleWatson123321]

Mike,

Good solution, worthy of a STAR - but allow me to correct "another Oops". ;-)

Your formula should be:
=SUM(IF(ISNUMBER(A1:A21),A1:A21))

Regards, Dale Watson dalwatson@gov.mb.ca

 

[OrangeWire]

thank you very much guys.

problem solved!