SUM Formula

[Riley]

I want to sum a range of 10 cells that already have formulas in them to auto
populate as I enter data elsewhere......If I want to sum these cells before
they have all been populated, I get a "N/A" return. Can someone tell me how
to sum these cells and ignore the cells that don't have data yet?

 

[Gary''s Student]

=SUM(IF(ISERROR(C1:C10),"",C1:C10))

This is an array formula that must be entered with CNTRL-SHFT-ENTER rather
than just the ENTER key

 

[Teethless mama]

=SUMIF(C:C,">0")

 

[Riley]

Thank you!!! I just tried this and it worked. You just saved me a lot of
time...I appreciate your help. Have a great day

 

[Gary''s Student]

Thanks for the feedback.

 

[xlmate]

try =SUMIF(B2:B11,">0"). Adjust the range to suit yours or you can use B:B for
whole column

Hope this help

cheers,

 

[Shane Devenshire]

Hi,

Here is a short array formula that works if the errors you are getting are NA:

=SUM(IF(ISNA(A1:A4),,A1:A4))

if not then, anothe short solution

=SUM(IF(ISERR(A1:A4),,A1:A4))

If you data is all positive numbers the SUMIF function is shorter and
doesn't need an array entry, but if any of the data are negative numbers then
stick with the array suggestions.

If these help, please click the Yes button.

Cheers,
Shane Devenshire

 

[T. Valko]

Try this:

=SUMIF(A1:A10,"<1E100")

 

[Ashish Mathur]

Hi,

You can also try

=sumif(range,"<0")+sumif(range,">0")

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com