Sum Indirect

Hi

Struggling with this indirect formula - got #ref error

exel 2003
A4 = sheet number

=SUM(INDIRECT("&A4&!"&M1:M14))

any one able to assist?

regards

-- 
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-functions/201004/1

0
BNT1
4/6/2010 11:36:27 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

3 Replies
974 Views

Similar Articles

[PageSpeed] 29

Try

=SUM(INDIRECT(A4&"!M1:M14"))

-- 

HTH

Bob

"BNT1 via OfficeKB.com" <u19326@uwe> wrote in message 
news:a61f79aa03a25@uwe...
> Hi
>
> Struggling with this indirect formula - got #ref error
>
> exel 2003
> A4 = sheet number
>
> =SUM(INDIRECT("&A4&!"&M1:M14))
>
> any one able to assist?
>
> regards
>
> -- 
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.aspx/excel-functions/201004/1
> 


0
Bob
4/6/2010 11:40:21 AM
Thanks Bob for the quick response - Worked a treat

regards

Bob Phillips wrote:
>Try
>
>=SUM(INDIRECT(A4&"!M1:M14"))
>
>> Hi
>>
>[quoted text clipped - 8 lines]
>>
>> regards

-- 
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-functions/201004/1

0
BNT1
4/6/2010 12:11:30 PM
Hi,

Is this what you want?
=SUM(INDIRECT(A4&"!M1:M14"))
-- 
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that 
introduces the fewest assumptions while still sufficiently answering the 
question.


"BNT1 via OfficeKB.com" wrote:

> Hi
> 
> Struggling with this indirect formula - got #ref error
> 
> exel 2003
> A4 = sheet number
> 
> =SUM(INDIRECT("&A4&!"&M1:M14))
> 
> any one able to assist?
> 
> regards
> 
> -- 
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.aspx/excel-functions/201004/1
> 
> .
> 
0
Utf
4/6/2010 12:22:01 PM
Reply:

Similar Artilces:

Summing the # of records
I have a spreadsheet that is has 10 groups. In the header of each group I'd like to total the # of records in that particular group, and at the bottom of the spreadsheet total the # of records in all groups combined. Does anyone know how to do this? Thanks in advance!! -- matt330 ------------------------------------------------------------------------ matt330's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28176 View this thread: http://www.excelforum.com/showthread.php?threadid=477503 What designates a group? -- HTH RP (remove nothere from the e...

sum function usin an IF statement
Bit short on detail, but maybe =IF(rng>10,rng) as an array formula, committed with Ctrl-Shift-Enter or =IF(rng1>some_val,rng2) again an array formula, where rng1 and rng2 are the same size. -- HTH RP (remove nothere from the email address if mailing direct) "jimk" <jimk@discussions.microsoft.com> wrote in message news:2873BFCF-598E-4956-808C-31C1638741A9@microsoft.com... > ...

sum calculation in subform based off of a query
I have a Main form that has date at the link to the subform. each record is a given DAY then the cleaning jobs are scheduled in the subform. Each cleaning job has an est. time. In the subform many people are scheduled to do many jobs. and same person is usually scheduled to do many jobs. Worked hard and this is the way the form subform - we want layed out. I would like to get a running total as each job is scheduled in the subform - sum of est. time by person(clockid). Have the subform sorted by person so they are all grouped together- tried putting sum of est time in footer. Many ...

Sum based on multiple conditions
I have Column A with a "stage" in it that will range from 1 - 8 I have column B with a "close date" in it that could be any date I have column C with a value in it. I need the sum of the values in C that have Close dates in any given range (ex. 10/1/2004 through 10/31/2004), that are in any given sales stage. I tried using SUMIF which seems to only work with 1 condition...so I can do =SUMIF(AA,1,CC) and it will give me the sum of the values that are in stage 1. SUMIF doesn't appear to work with ranges or multiple conditions though. =SUMPRODUCT((C1:C100>="...

Sum Function (was summing 13 cells to the left)
Trying to compose a formula that will only sum 13 cells to the left (13 months of data). When columns are added, I still only want to sum thirteens months of data. Can this be done without constanty changing my formulas? Make sense TIA Neil S. Hi see my reply to your old post -- Regards Frank Kabel Frankfurt, Germany Neil S. wrote: > Trying to compose a formula that will only sum 13 cells to the left > (13 months of data). When columns are added, I still only want to > sum thirteens months of data. Can this be done without constanty > changing my formulas? Make sense? ...

sum based on content of other cells
Cells A7:A49 have pull downs with 20 categories to pick from. Cells H7:H49 has the value in hours spent on that category. I would like to get the sum of hours based on each category, and put this value in another cell. Can someone please help me with this ? Thanks ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ You want to use the Sumif function. >-----Original Message----- >Cells A7:A49 have pull downs with 20 categories to pick from. >Cells H7:H49 has th...

Summing with nulls
My statement select sum(column) from ( select column from table union select column2 from table2) as T Now the second part of the union returns a null. How do i sum with a null i have tried using case when then end but this does not work. The sum does return a value but it is incorrect. When the second part of the union statement is null i want to get the sum of the first part. Thanks try it with "isnull" select sum(isnull(column,0)) from yourtable "Cdudej" <jedfletcher1@gmail.com> wrote in message news:5df50a11-89a7-42d6-b92b-e611910394e6@k19g200...

Sum function
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I am trying to put together a spreadsheet for a business plan and am having problems with the autsum and autofill functions. Firstly, even though I do a simple autosum, if I change a value in a cell, the sum does not change. Secondly, if I try to autofill across columns, the values of those columns simply mirror the value of the first. In other words it copies it. I am at a loss how to get round it as I have been working w Excel for years and have never had this problem. I have experimented with a new worksheet with ...

SUM cells in column based upon criteria in two others.
Can not seem to put together a formula that accomplishes the following: Column J contains contributions by individuals for 2010 Column I contains contributions by individuals made in 2009 Column N contains an indicator (+ - or 0) of the increase or decrease over the two years Column P contains a designation of which members are new members. I need a formula that looks at column I cells against column J cells, totals the contributions made be each of the criteria in column N, but does NOT include those with any designation in column P. Example of the data: I ...

can't get "sum" to add up properly
Hi, I'd appreciate any help with my problem. I have the following A B C 1 Benefit Coverage Amount Monthly Premium 2 Employee Life .21/$1,000 $67,000.00 $14.07 3 Dependent Life (if applicable) family $2.00 4 Employee AD&D .04/$1,000$67,000.00 $2.68 5 LTD 2.58/100 $1,529.00 $39.45 6 Dental family 47.86 7 Health family 48.90 8 EAP (if applicable) yes $2.20 9 ...

Criteria sum in report
I have a report looking something like this (just an example): Id Value 1 2 2 4 3 1 4 2 5 3 In the report footer I want a field that gives me the sum for values with id's between 2 and 4. Earlier I have solved this by making an extra query and a subreport, but I'm wondering if it could be solved otherwise. Maybee by using a sql sentence for the controllsource property on an ordinary textbox. Appreciate any suggestions -- regards hallgeir Try something like this in the Control Source of a text box in the Report Footer section: =Sum...

Sum
If I highlight several numbers in a column, Excel will indicate the sum. But it always shows that sum as a positive number, even if it's actually negative. Is there any way to change this behavior? Hi, Are you talking about the sum in the status bar. It does display the sum as negative number as well. Govind Steve wrote: > If I highlight several numbers in a column, Excel will indicate the > sum. But it always shows that sum as a positive number, even if it's > actually negative. Is there any way to change this behavior? Govind <adgraj1@indiatimes.com> wrote: &...

Sum the value across multi worksheets
I would like to sum the value of several worksheets. Exmple; 1st Qtr, 2nd Qtr... etc. Have total of all 4 Qtrs. appear on 4th Qtr. worksheet. I have tried using =SUM(sheet1:sheet4!F35) Thanks, that WILL work if the value desired is in f35 on each sheet. It will sum sh1,sh2,sh3,sh4 -- Don Guillett SalesAid Software Granite Shoals, TX donaldb@281.com "Keith F." <kfindley@evapar.com> wrote in message news:02de01c35224$466a55a0$a401280a@phx.gbl... > I would like to sum the value of several worksheets. > Exmple; 1st Qtr, 2nd Qtr... etc. Have total of all 4 > Qtrs. app...

update sheet name in conditional sum every month
I have files with monthly data. This data has different categorie (columns). I am summarizing this data in a sheet using conditional sum Now when new data comes in every month I have to go into each cell wit these formulae to update the formula with new sheet name with new data Is there any easier/faster/better way to do this? Here is an example of formula in one cell (obviously when I copy th formula excel gets rid of { brackets but those are there in th formula)......and I have formulae like this in about 40 cells that need to update every month manually. =SUM(IF(Sheet1_Dec!$J$2:$J$2859=...

Show Blank is cell value=0 but count as a zero in sum. How to format this cell ?
I some Excel 2003 cells in a columns I have occasionally a value of 0.0 (=zero). I want to format these cells to display blank (not "0.0") if the value is 0. But in the columns sum line ("=SUM(C4:C14)") this cell should be treated as a value of 0. How do I format this cell in Excel 2003? Markus One way 0.0;0.0; Regards, Peo Sjoblom "Markus Obermayer" wrote: > I some Excel 2003 cells in a columns I have occasionally a value of 0.0 (=zero). > I want to format these cells to display blank (not "0.0") if the value is 0. > But in the columns...

Sum of Column
I am trying to print a physical inventory with the total cost at the bottom. How can i do this? Thanks ...

How do I add a column to a sum formula for every row ie row 1 nee.
I need to add a column value (ie column L) to every row. Ie row 1 formula is sum(A1+B1+C1+D1+E1+F1......) I need to add +L1 to this formula. That is the easy part. Can I add L1, L2, L3 etc to there corresponding rows at once or do I have to do each one individually. Ie I have to then manually go to row 2 to add L2 then row 3 to add L3 etc. Looking for a shortcut If you select the cell with your formaula in it there should be a drag handle on the bottom right corner, just left mouse click and drag down the rows required formula will reference automatically. Hope this helps "ba...

Sum total hours worked in excel
I want to add the total hours worked in excel that I book for the month for example: A B C Start Time End Time Total 08:00 16:00 08:00 08:00 14:30 06:30 08:00 15:00 09:30 Total 00:45 It stop at 23:59 and I want it to go on like a total sum, wat formula must I use to do my total sum calc for my time? please help Frankie e-mail: frankiedeconing@harmony.co.za You should format the cells in column C as [h]:mm - that way they will not wrap round above 24 hours, but ...

If, say, =SUM(I2:I10) totals "0", how to change formula to put a "0" in I11?
I know this is going to be simple but everything I've not got anything to work. How can we change formula, pls, to have "=SUM(I2:I10)" show up as "0" in I11 if there aren't any values anywhere in I2 to I10? Thanks. :oD If there are no values to sum the formula should already be returning 0. What result are you getting? Biff "StargateFanFromWork" <NoSpam@NoJunk.com> wrote in message news:OV$7dJLnHHA.3704@TK2MSFTNGP02.phx.gbl... >I know this is going to be simple but everything I've not got anything to >work. How can we change f...

How to sum top 5 numbers from the column of numbers
I have about 100 columns with numbers. In every column there is 25 numbers. I want to sum top 5 numbers in every column. How to do it without sorting every column. =sum(large(column,{1,2,3,4,5}) "Martin" wrote: > I have about 100 columns with numbers. In every column there is 25 numbers. I > want to sum top 5 numbers in every column. How to do it without sorting every > column. > ...

Conditional sum, wizard or otherwise
Hello, All! There was a question recently on numerical integration. I can see how to do it using Simpson's rule and the most appropriate function would be SUMIF but, if there are two columns, x and y, I cannot think how to sum the y-values when x is odd using ISODD (or equally ISEVEN). Can someone point me in the right direction? I could write an expression using IMOD but that seems clumsy. Thanks in Advance! James Silverton Potomac, Maryland, USA James wrote to All on Fri, 20 Jan 2006 18:36:42 -0500: JS> There was a question recently on numerical integration. I JS> ca...

Pivot Table Sum
I have created a PivotTable using data in another spreadsheet. For some reason when I try to sum the data in the Data Field, all the values turn to zeros. But if I leave it as a count the information is correct. Is it because the data in the original cells are formulas? Any help or suggestions would be greatly appreciated. Thanks -- RedFive One way to test your theory is to take the data source & change them to values instead of formulas. I've seen this occur when trying to aggregate blank data. -- http://www.ExcelHelp.us ed@ExcelHelp.us 888-MY-ETHER ext. 01781474 &q...

sum() using indiect()
Can anyone post a formula that successfully uses SUM(INDIRECT("concatenated cell range")) where the path isn't local to the sheet it is on? ex: A1 = [TestBook.xls]Sheet1!B1 A2 = [TestBook.xls]Sheet1!B10 =SUM(INDIRECT(A1&":"&A2)) .....can you get that one to work?? See reply in .Functions Biff "Grymjack" <dcanham@rochester.rr.com> wrote in message news:nghng.50667$3B.13187@twister.nyroc.rr.com... > Can anyone post a formula that successfully uses > SUM(INDIRECT("concatenated cell range")) where the path isn't local t...

sum by skiping one cell
I want to sum only even cells from A1 : A1000 I used =a2+a4+a6+a8+…………..+a1000 Used this formula n cell b1 But now I want to solve this problem by some short formula Help me thank you See your post at worksheet.functions. Faisal Yameen wrote: > I want to sum only even cells from A1 : A1000 > I used =a2+a4+a6+a8+…………..+a1000 > Used this formula n cell b1 > But now I want to solve this problem by some short formula > Help me thank you > or even here -- HTH Bob Phillips "Aladin Akyurek" <akyurek@xs4all.nl> wrote in message news:41F3C09D.606080...

Sum specific row values
Hi there, i have a problem trying to sum some values that are in certain rows...The table looks like Col1 Col2 Col3 Col4 ----------------------------------- P X 112 a P X 100 b P X 26 c P X 15 b P X 13 d P X 10 a Ok, the result must be a table that looks like Col1 Col2 Col3 Col4 ----------------------------------- P X 122 a P X 115 b P X 26 c P X 13 d Please help...i need an sql query, if possible... THANX! ...