Excel SUM formula problem (emergency)

Eglinton

SOrry if this is the wrong place but I need to sum a huge number of cells in a column in excel. Many of the cells have Divs in them. Is there any way to sum them and ignore the divs? Haven't time to fix them. Too complex for now.

Major thanks to anyone who can resolve asap.

FSL

As far as I know the sum function in Excel ignores anything which does not resolve to a number. That is if a cell within the range contains a formula that returns a number the number is added to the sum,if the cell contains a string or a formula which returns a string it is ignored.

Eglinton

No. If there's a cell that's showing a Dix/0 error the Sum formula won't work. There has to be some way to ignore these though

carveone

Eglinton wrote: »

No. If there's a cell that's showing a Dix/0 error the Sum formula won't work. There has to be some way to ignore these though

Hum. Interesting. If the values are in column B (say) you could create a new column in C with the value (in C1 first of course - sorry for the newbie treatment):

=IF(ISERROR(B1),0,B1)

then copy it down (usual way by dragging cell corner).

Then sum column C. Um... Can't think of a better way at the moment....

Conor.

carveone

I found another method. Not sure you'll like it though!!!

It uses the DSUM function. Uh.. cannot copy and paste my excel example I guess so I'll have to talk you through it...

Blank worksheet:

A1: Numbers
A2: 1
A3: 2
A4: =3/0
A5: 4
etc
A10: 9

Right. Now.

A12: =SUM(A1:A10)
gives you an error.

Add in:
B1: Numbers
B2: <>#DIV/0!

Note that B1 must be the same label as A1 and type B2 as is (don't put anything in quotes!).

Now add:

A13: =DSUM(A1:A10,"Numbers",B1:B2)


This gives you the right answer. The B column is a "criteria" and selects the database items you are interested in...

Look up DSUM in Excel help. The Criteria bit is quite scary. I mean detailed

Does this help!

Conor.

Eglinton

That sounds like it could be a runner alright. I tried it though and can't get it to work. I think I've followed your formulae. Screenshot below. I've highlighted in yellow whats in the cells beside them. The #Div/0 in B2 is just text.

Eglinton

GOT IT! I was missing the <>. Doh!

Cheers for the help. I'll try it tomorrow at work.

rockal

Another option is to use
=SUMIF(A1:A15,">0")
This will ignore anything that is not a positive number.

Eglinton

rockal wrote: »

Another option is to use
=SUMIF(A1:A15,">0")
This will ignore anything that is not a positive number.

Yeah that's a very good one. The only thing is it won't work if you want to sum cells that are not in a range. For example this won't work:
= SUMIF(A1,A2,A5,A7,">0"). You'd have to put a sumif and ">0" with every cell reference.

Any way around this? Might be just a slight modification to the formula maybe.

rockal

All I can think of at the moment is:
=SUMIF(A1:A4,">0")+SUMIF(A6:A10,">0") etc.