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

  • Thread starter Thread starter Struggling in Sheffield
  • Start date Start date
S

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(July:December!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.
 
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.
 
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
 
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.
 
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.
 
Thanks...
Struggling in Sheffield said:
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.
Click to expand...
 
Back
Top