Summing with Blanks

[kenpcli]

How do i sum a column but omit if the cell is blank? This is the formula I have now:

=SUM(L87,L83,L79,L75,L71,L67,L63,L59,L55,L51,L47,L43,L39,L35,L31,L27,L23,L19,L15,L11)

I can't do the sum(L11:L87) because it gives be the numbers in between as well.

Thank you,

 

[steve the fish]

Dont know quite what you mean by if the cell is blank but maybe:

=SUMPRODUCT(L11:L87,--(MOD(ROW(L11:L87),4)=3))

 

[kenpcli]

Dont know quite what you mean by if the cell is blank but maybe:

=SUMPRODUCT(L11:L87,--(MOD(ROW(L11:L87),4)=3))

If the account the report is running has no data it comes back blank, but I just need it to add the subtotal lines which are above regardless if they are blank or have a total. The formula you provided doesn't give me the correct total.

 

[steve the fish]

The formula i gave you is the same as =SUM(L87,L83,L79,L75,L71,L67,L63,L59,L55,L51,L47,L43,L39,L35,L31,L27,L23,L19,L15,L11) so if it isnt that you want what is it you want? SUM wont sum blanks anyway..

 

[kenpcli]

The formula i gave you is the same as =SUM(L87,L83,L79,L75,L71,L67,L63,L59,L55,L51,L47,L43,L39,L35,L31,L27,L23,L19,L15,L11) so if it isnt that you want what is it you want? SUM wont sum blanks anyway..

Correct, so how do I get a formula that will add those cell, but ignore them if they are blank?

 

[steve the fish]

What do you mean ignore a blank? Your sum does ignore blanks.

 

[kenpcli]

What do you mean ignore a blank? Your sum does ignore blanks.

My sum comes back #ref .

 

[steve the fish]

Then you either have cells with reference error within your range of cells or you have deleted some rows and if you look at the formula in the formula bar you will see #REF ! somewhere.