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

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

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

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

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


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

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

Try simply...

=SUM(July:December!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(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.

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

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


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

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

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

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

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(July:December!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(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.

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

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(July:December!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(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.