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

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,755
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Oct 14, 2003
Messages
1,487
Hi Verlaine

How about the SUMIF function?

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

You can adjust the criteria to suit.

Regards
 

verlaine

New Member
Joined
Aug 16, 2002
Messages
43
try different sumif formulas but have no success,

is it possible to make sumif with isnumber as criteria ?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,755
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,755
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Dec 2, 2002
Messages
1,938
Office Version
  1. 2019
Platform
  1. Windows
Or try ….

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

HTH
 

GorD

Well-known Member
Joined
Jan 17, 2004
Messages
1,446
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,755
Office Version
  1. 365
Platform
  1. Windows
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.
 
Learn Excel from Bill Jelen

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

Forum statistics

Threads
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.
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
Top