# sum

#### verlaine

##### New Member
I would like to sum a column who contains numbers, n/a or other characters.

How can I do to have the sum of this numbers ?

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

#### Joe4

Check out the SUMIF function in Excel help. You can set the conditions for the values you want to sum. The conditions you are checking can even be in a separate column from the one you are summing.

#### Glaswegian

##### Well-known Member
Hi Verlaine

Eg =SUMIF(F3:F9,">0")

You can adjust the criteria to suit.

Regards

#### verlaine

##### New Member
try different sumif formulas but have no success,

is it possible to make sumif with isnumber as criteria ?

#### Joe4

What are examples of the values you are trying to sum? If there are text strings mixed in, it shouldn't cause a problem as the SUM formulas will skip them. If you have some error values (i.e. #DIV/0), then we may need to clean up the data first.

If you have some errored values in your SUM, how are those values calculated? We may be able to fix them at the root.

#### verlaine

##### New Member
I have this formula on this column :

=VLOOKUP(B2;Sheet1!\$B\$1:\$I\$44;8;FALSE)

results are
138'506.00
75'000.00
50'000.00
17'300.00
20'000.00
16'140.00
¦
¦
14'000.00
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A

#### Joe4

Try changing your VLOOKUP formula to something like:

=IF(ISERROR(VLOOKUP(B2;Sheet1!\$B\$1:\$I\$44;8;FALSE),0,VLOOKUP(B2;Sheet1!\$B\$1:\$I\$44;8;FALSE))

This will return a zero if it cannot find anything. Then you should just be able to do a simple SUM without any problems.

#### bosco_yip

##### Well-known Member
Or try ….

=SUMIF(C2:C20,"<>"&"#N/A")

HTH

#### GorD

##### Well-known Member
Jimsky, just wondering about your thinking on using iserror rather than isna, for the instance of a lookup value not being found. I always think that is dangerous as the iserror will mask any problem, formula syntax etc without you knowing, rather than ISNA which is more specific to a non found value.

Just wondering if there is any reason for the iserror that I am unaware of?

#### Joe4

Jimsky, just wondering about your thinking on using iserror rather than isna, for the instance of a lookup value not being found. I always think that is dangerous as the iserror will mask any problem, formula syntax etc without you knowing, rather than ISNA which is more specific to a non found value.

Just wondering if there is any reason for the iserror that I am unaware of?

Probably the same reason why you can't spell my name; its too early in the morning and I haven't had any caffeine yet!

Seriously, you are correct. It would be much better to use ISNA than ISERROR so you could detect other errors which may occur.

Replies
4
Views
120
Replies
0
Views
218
Replies
3
Views
252
Replies
1
Views
60
Replies
3
Views
145

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

1,151,560
Messages
5,765,095
Members
425,258
Latest member
brentmitchell

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

### Which adblocker are you using?

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

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