Sum across spreadsheets & ignore blanks to get rid of #VALUE! erro

[Struggling in Sheffield]

Hi,
I'm trying to sum the total of the same cell in six adjacent spreadsheets
(named 'July' to 'December') onto a separate summary sheet at the end. My
problem is that many of the summed cells are blank which means my formula
gives me a #VALUE! error. Current formula is:

=SUMIF(Julyecember!A108,"<>""")

After trawling the forum for the answers to similar problems I came up with
the formula above (also tried several variations) but it still returns the
#VALUE! error when one or more of the summed cells is blank.

In addition, if all of the summed cells are blank, it would be nice to get a
blank cell or £0.00 returned in the summary cell.

Thanks for looking and please help if you can.
Cheers,
Steve.

 

[Gary''s Student]

If you build a local table, you can use an array formula. Say in G1 thru G6
we insert:
=July!A108
=August!A108
etc.

Then:
=SUM(IF(ISERROR(G1:G6),"",G1:G6))
This must be entered with CNTRL-SHFT-ENTER rather than just the ENTER key.

 

[Pecoflyer]

Hi, there must be another problem somewhere, the SUM function is no
affected by blank cells or text.
By registering ( free) on our forum , you could post a sample of you
data.
It would be easier to find what's wrong

Hi,
I'm trying to sum the total of the same cell in six adjacen
spreadsheets
(named 'July' to 'December') onto a separate summary sheet at the end
My
problem is that many of the summed cells are blank which means m
formula
gives me a #VALUE! error. Current formula is:

=SUMIF(Julyecember!A108,"<>""")

After trawling the forum for the answers to similar problems I came u
with
the formula above (also tried several variations) but it still return
the
#VALUE! error when one or more of the summed cells is blank.

In addition, if all of the summed cells are blank, it would be nice t
get a
blank cell or £0.00 returned in the summary cell.

Thanks for looking and please help if you can.
Cheers,
Steve

--
Pecoflye

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows fil
upload ->faster and better answers

*Adding your XL version* to your post helps finding solution faste

 

[Domenic]

Try simply...

=SUM(Julyecember!A108)

Hope this helps!

http://www.xl-central.com

Struggling in Sheffield

 

[Struggling in Sheffield]

Hi,
Thanks for getting back to me but it's sorted now.
Can't believe how simply it was solved, I've used Domenic's answer although
how I never tried such a basic formula I'll never know.
Too many hours at it had obviously dulled the brain!
Thanks again

 

[Struggling in Sheffield]

Hi,
Thanks for trying to help but it was sorted very simply by Domenic's answer.
Can't believe how hard I worked trying to solve it (hours!) , and then the
most basic of formulas comes up trumps. I tried the SUM solution but must
have made a silly error somewhere because it wouldn't work. Not one of my
better days. Ho hum.
Thanks again.

 

[Struggling in Sheffield]

Hi,
Thanks very much for that, yes it worked. I tried for ages to find a
solution and tried SUM-ing it very early but it wouldn't work. Can only think
I made some basic error when I entered the formula. Some of the formulas I
was constructing after two hours were rocket science (couldn't get them to
work either!).
I'm sure I'll have better days.....
Thanks again.

 

[Roger MacInnis]

Thanks...

Hi,
Thanks very much for that, yes it worked. I tried for ages to find a
solution and tried SUM-ing it very early but it wouldn't work. Can only
think
I made some basic error when I entered the formula. Some of the formulas I
was constructing after two hours were rocket science (couldn't get them to
work either!).
I'm sure I'll have better days.....
Thanks again.