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.
0
3/31/2009 6:05:03 PM
excel.newusers 15348 articles. 2 followers. Follow

7 Replies
498 Views

Similar Articles

[PageSpeed] 43

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.
0
GarysStudent (1572)
3/31/2009 6:28:07 PM
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/showthread.php?t=81223

0
3/31/2009 6:39:23 PM
Try simply...

=SUM(July:December!A108)

Hope this helps!

http://www.xl-central.com

In article <93606D49-B0C8-48C5-A03E-1A0AF031DFF5@microsoft.com>,
 Struggling in Sheffield 
 <StrugglinginSheffield@discussions.microsoft.com> 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.
0
someone528 (62)
3/31/2009 9:56:46 PM
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/showthread.php?t=81223
> 
> 
0
3/31/2009 11:47:01 PM
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.
0
3/31/2009 11:53:01 PM
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 <93606D49-B0C8-48C5-A03E-1A0AF031DFF5@microsoft.com>,
>  Struggling in Sheffield 
>  <StrugglinginSheffield@discussions.microsoft.com> 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.
> 
0
4/1/2009 12:10:01 AM
Thanks...
"Struggling in Sheffield" <StrugglinginSheffield@discussions.microsoft.com> 
wrote in message news:190994BF-46F8-41A5-A1E3-417D3E3ED940@microsoft.com...
> 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 <93606D49-B0C8-48C5-A03E-1A0AF031DFF5@microsoft.com>,
>>  Struggling in Sheffield
>>  <StrugglinginSheffield@discussions.microsoft.com> 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.
>> 


0
4/8/2009 3:51:43 PM
Reply:

Similar Artilces:

how do I get my email to come into "inbox" instead of aol inbox
I recently purchased a new PC. I installed office 2007 . When setting up the email I used the setup proceedure to automatically set things up. In my previous outlook all my email came into my inbox. Now it goes to "inbox in myemail.com. I would like to get my email to show up in my inbox and be filtered by Outlook. How do I change this? On 3/11/2010 11:33 AM, Anna wrote: > I recently purchased a new PC. I installed office 2007 . When setting up > the email I used the setup proceedure to automatically set things up. > > In my previous outlook all my email...

Can't get help for word macros
I am writing simple macros for Microsoft Word 2007. When I am in the document and want to work on the macro, I press Alt+F11 and that opens up the macro workarea. If I click on the “Microsoft Visual Basic Help” and enter almost anything, it is not found. I have even tried simple things like Save or File and they aren’t found. I have gone into Tools -> References and made sure things like “Microsoft Development Environment 8.0” and “Microsoft Development Environment VB .Net” are checked. I know I don’t have something set right but I can’t figure out what. Anyone have a...

Using SUMIF i want to say IF A=1 & B=2 then sum C any help pleas
I want to single out a sum that has two common detonators row A and B, IF row A=1 and row B=2 then sum row C Try this, adjust the range to match your data: =SUMPRODUCT(--(A1:A100=1), --(B1:B100=2), C1:C100) Does that help? -- "Actually, I *am* a rocket scientist." -- JB (www.MadRocketScientist.com) Your feedback is appreciated, click YES if this post helped you. "Peter" wrote: > I want to single out a sum that has two common detonators row A and B, IF row > A=1 and row B=2 then sum row C That's quite an explosive worksheet you have<...

How to change the default value in "Filter On" field (Microsoft CRM 3.0)
Hi. When I open a contact in CRM, I can view the sales activities (Opportunities, Quotes, Orders, and Invoices) under "Sales" session. Is there any way I can change the default value under "Filter On" drop down menu? For example, when I click on Invoice, I want "Filter on:" drop down list select "Paid" instead of "Active" Thanks for your advice. You do the following: Settings -> Customizations -> Customize Entities Select the contact entity Then you will see a list of views and a form. One of those views is set ...

onChange code to automatically select a lookup value
Hi, When i create a new case and select a Customer from the lookup value, i then want the most recent contract to be automatically selected, and then from this the contract line to be automatically selected. But i am having problems with trying to write code with the onChange event to do this because i dont know how to select a value of a lookup list. Please help :) Mike ...

Opening Excel spreadsheet
1. I am using Excel 2007. I save it in 2003 format. When I open it up again I get Excel but can't see the workbook until I minimize the window at which time it comes up into view automatically. Does anyone know why this is occurring? 2. In what I beleive is an extension of 1. above I have linked the spreadsheet to a Word 2007 document. When I open the Word document the Excel file seems to automatically open with it but when I switch to the Excel file from the Word file I am unable to make the worksheets visible although the Excel window is there. ...

Can't get text to repeat on monthly pages in Publisher calendar
I have text in the January 2010 page of a Publisher calendar but the remaining months do not show the text I have entered on the January page. How do I get the text to repeat in the text boxes on remaining months? You will have to enter the information manually for every month. It will be easier if you create a text box for your events, you then can copy/paste the text box month to month. -- Mary Sauer http://msauer.mvps.org/ "allen" <user@msgroups.net/> wrote in message news:ur4pblSeKHA.2596@TK2MSFTNGP04.phx.gbl... >I have text in the January 2010 page of a...

sending an excel spreadsheet
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel How do i send my single excel sheet, in an attachment without the numerous blank sheets showing up too? I have tried to send a file which has only one sheet of data and numerous blank sheets. Although it prints as one sheet, the attachment on an email will send the data sheet along with a bazillion additional blank sheets. This has never happened before so I don't know what is happening or how to fix it. Many Thanks. When you attach a file to an email message you attach the *entire* file � you can't just at...

Second Exchange 2003 Server Across WAN
We have two offices that share the same AD Domain. We have different domains for e-mail addresses. Currently all of our e-mail is hosted in the one location. I would like to have their e-mail be local to them. I don't really know the steps, but if someone could lead me I'd appreciate it. I'm guessing: 1) Install E2003 on the remote branch server 2) Bring it up to date with same service packs that are applied in the local branch. 3) Move the mailboxes from one server to another (is this possible) 4) set up the DNS server to point to the second exchange for their separate e-...

Get A Free IPOD
I just got my IPOD in the mail..Its Fast Free and Easy. http://tinyurl.com/6c86j7 ...

Default Weight in Pivot Table Charts keeps getting reset
Hi, I have created a Pivot Table chart and I am using basic line chart to display the data...I would like to make the line weight 2 points heavier than the standard but each time I select new values from the Pivot Table chart it resets to the default....where do you set this to remain at the values entered... Any help appreciated.... Scott ...

convert word document to excel spreadsheet
I have many word documents (looks like a resume cover letter) and wish to convert it into excel/accesss db for easy retrieval? I probably have to rename the filename of these documents into job ref no. or personal id number. I have tried importing to excel but it look abit mess up , becos it has paragraphs, indentation. I wish to have a database system that allow me to retreive Personal id and view their cover letter? karen Hi Karen, Perhaps you only want to add a link to the Word document in a cell in your worksheet? Go to an empty cell and use the key combination {ctrl}{K} to display t...

When I open Entourage now, I get the message, continuously, until I close Entourage, I get this about every 30 seconds, PLEASE HELP
Microsoft Error Reporting log version: 2.0 Error Signature: Exception: EXC_BAD_ACCESS Date/Time: 2010-04-13 14:04:58 +0300 Application Name: Microsoft Sync Services Application Bundle ID: com.microsoft.entourage.syncservices12 Application Signature: MSS1 Application Version: 12.2.3.091001 Crashed Module Name: unknown Crashed Module Version: unknown Crashed Module Offset: unknown Blame Module Name: unknown Blame Module Version: unknown Blame Module Offset: unknown Application LCID: 1033 Extra app info: Reg=en Loc=0x0409 Thread 0 crashed: # 1 0x6e756f63 in ( + 0x00000...

Calculate sum in multiple files
I have over 300 individual excel files that contain job sheets for sites that my company visits. The layout of each file is identical and there is a particular column in each file containing 5 cells that requires summing. I need to create a new file with site names (the file names same as site names) in column A, and the sum of each of the 5 cells in all these files. Is there a way to do this without opening every single file and summing the cells back to the new sheet? Hope I've been clear enough ;) -- Tomos ------------------------------------------------------------------------ ...

Retrieving Value From Subform Only Works In Break Mode 10-17-07
The code: ------------------------------------------------------------- 2550 With Me.subSecurity.Form 2551 If Me.subTrades.Enabled = True Then 2552 Me.subTrades.Form!txtParTotal.Requery 2553 curParTotal = Me.subTrades.Form!txtParTotal.Value 'MsgBox curParTotal 2554 !txtParTotal.Value = curParTotal 2555 !txtParTotal.Requery 2556 End If 2559 End With ------------------------------------------------------------- If I put a break on line 2552 or any line above it, the value gets retrieved and everything works a-ok. But with a break on line 2553 or no ...

How to move the sum formula
Hi, I have to copy the sum formula from one column to the next I used (the range of second coloumn)=range of first column.formula The reason is I would like to roll forward the formula of month to another month Range(Cells(x, i + 2), Cells(y, i + 2))= Range(Cells(x, i), Cells(y, i)).Formula it works well except the sum formula cells. the sum formulas inside is again referencing the old location.what to do.Please help me out. Thanks, Santhosh I think it depends on what formula is in the original cell. Maybe... Range(Cells(x, i + 2), Cells(y, i + 2)).formular1c1 _ = Range(Cells(x, i), Ce...

How do you get an Assistant?
What Help -> Show the Office Assistant. -- HTH, Barb Reinhardt "Frogger" wrote: > What "Frogger" <Frogger@discussions.microsoft.com> wrote in message news:74E12FA3-D01E-4E2F-822B-66CD251F12AC@microsoft.com... > What see your HR department.... ...

How do I get a word count in Excel XP
I am translating and need a word count. Excel doesn't have a built-in word counter. You'd have to loop through each cell, determine how many words in each cell, and sum these. For example, Sub AAA() Dim WordCount As Long Dim Rng As Range Dim S As String Dim N As Long For Each Rng In ActiveSheet.UsedRange.Cells S = Rng.Text N = 0 If S <> "" Then N = Len(S) - Len(Replace(S, " ", "")) + 1 End If WordCount = WordCount + N Next Rng MsgBox WordCount End Sub -- Cordially...

How do I get it to appear on the page as it does in Print Preview?
I'm planning to e-mail a promotion flyer department-wide but I can't get it to where the lines and text boxes are invisible when displayed in the message. When I got to print preview, the lines/text boxes aren't there. How do I get those lines to disappear while I'm viewing it on the page? Nevermind...I figured it out :) Thanks! "Officeworker777" wrote: > I'm planning to e-mail a promotion flyer department-wide but I can't get it > to where the lines and text boxes are invisible when displayed in the > message. When I got to print previe...

How to get started with VBA to Import a Text.txt file
The file is located in the C:\ drive. C:\Text.txt I need to create a button to call a procedure to import a text file. Please point me in the right direction to get started.... I know how to create buttons to call macros and/or procedures ... I need help on getting started in VBA - OPENFILE C:\Text.txt ???? Thanks. Try something like: DoCmd.TransferText acImportDelim, , "MyTable", "C:\Text.txt", True use False if the first row doesn't have fieldnames -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmv...

I need to combine data from 2 spreadsheets
I'm not good with macros, so if we could do this simply, I would appreciate it. Spreadsheet 1 I have a list of all my accounts The headings I need from that spreadsheet are: Column A id Column B name Column C billing_address_street Column D billing_address_city Column E billing_address_state Column F billing_address_postalcode Column G billing_address_country Spreadsheet #2 This is a list of registration numbers Column A R/N Column B Product Column C Account...

Template opens blank
A colleague is trying to open a template sent to him by our company. When he opens it, all he sees is grey - no document, no gridlines, no logos, no nothing. No one else is having a problem with the file. I had him check his security level - fine. His view options are fine. He seems to be able to open other templates with no problem. We're all running Office 2003. Any suggestions on what else I can have him do? Maybe it's just off the visible screen. Open the template (or create a new workbook based on that template) and then window|arrange|tiled (and resize that window manual...

price field value varies function of quantity field same record
My product has varying price/unit depending on quantity (i.e 1-3 units = 50$/unit, 4-10 units= 45$/unit etc ). Do I need a new record in product table for each price structure or is there a way of having a range of numbers in a table field. Then when client chooses quantity I want to select the correct price in a query. (i.e. select 5 units, so price is 5 x 45$). That part isnt difficult i think. Any help would be positive karma. One product can have many prices (depending on quantity), so you have a one-to-many relation between parts and prices. You need this ProductPrice table in add...

M2004: One of my funds keeps getting updated as having a price of $0
The fund is EUROX. What is odd is that it is only this fund; everything else has been updating properly. I was recently talking to someone who is also a Money user and also owns EUROX, and he reports the same problem! It's been happening for a few weeks... how do we get this fixed? Manually updating the prices is getting old. Thanks. ...

counting across multiple worksheets
I am trying to count the number of occurances for a unique value (0) in a common column (F12) across multiple worksheets (Dominion1 to Dominion 19). Can't seem to find a formula that works. Any help would be appreciated Thanks in advance Gordon One way =SUMPRODUCT(COUNTIF(INDIRECT("'Dominion "&ROW(INDIRECT("1:19"))&"'!F12"),0)) -- Regards, Peo Sjoblom "Gordon Walsh" <gordonl.walsh@ns.sympatico.ca> wrote in message news:80FF2410-60F6-467D-89D6-07A99D2B46C2@microsoft.com... > I am trying to count the number of occura...