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

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.

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.

--
Gary''s Student - gsnu200842


"Struggling in Sheffield" wrote:

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.

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


Struggling in Sheffield;290614 Wrote:
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.


--
Pecoflyer

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

*Adding your XL version* to your post helps finding solution faster
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=81223

Try simply...

=SUM(Julyecember!A108)

Hope this helps!

http://www.xl-central.com

In article ,
Struggling in Sheffield
wrote:

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.

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

"Pecoflyer" wrote:


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


Struggling in Sheffield;290614 Wrote:
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.


--
Pecoflyer

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

*Adding your XL version* to your post helps finding solution faster
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=81223

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.

"Gary''s Student" wrote:

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.

--
Gary''s Student - gsnu200842


"Struggling in Sheffield" wrote:

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.

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.

"Domenic" wrote:

Try simply...

=SUM(Julyecember!A108)

Hope this helps!

http://www.xl-central.com

In article ,
Struggling in Sheffield
wrote:

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.

Thanks...
"Struggling in Sheffield"
wrote in message ...
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.

"Domenic" wrote:

Try simply...

=SUM(Julyecember!A108)

Hope this helps!

http://www.xl-central.com

In article ,
Struggling in Sheffield
wrote:

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.