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.

 

[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.