sum

verlaine

New Member
Joined
Aug 16, 2002
Messages
43
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

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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.
 
Upvote 0
Hi Verlaine

How about the SUMIF function?

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

You can adjust the criteria to suit.

Regards
 
Upvote 0
try different sumif formulas but have no success,

is it possible to make sumif with isnumber as criteria ?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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! :LOL:

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

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

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