Indirect range in SUMPRODUCT?

If I have a formula that defines arange in cell A1
="column"&"row"&":"&"column"&"row"
and another range defined in the same way in B1,
I can reference them with
=SUMPRODUCT(INDIRECT(A1),INDIRECT(B1))

However I can not insert the formula in A1 instead of the 
reference to A1 in the SUMPRODUCT(INDIRECT()) function. Is 
there a way force this?

I ask because SUM, COUNTIF, and others will accept the 
range definition instead of the reference to the range.

Of course this won't save me much space, just want it in a 
couple places to make speadsheets a little neater.
Thanks
1
hightide (27)
2/16/2005 8:07:41 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
440 Views

Similar Articles

[PageSpeed] 30

Hi!

Indirect will not accept arguments that evaluate as 
formulas. It will only accept arguments that evaluate as 
references.

Biff

>-----Original Message-----
>If I have a formula that defines arange in cell A1
>="column"&"row"&":"&"column"&"row"
>and another range defined in the same way in B1,
>I can reference them with
>=SUMPRODUCT(INDIRECT(A1),INDIRECT(B1))
>
>However I can not insert the formula in A1 instead of the 
>reference to A1 in the SUMPRODUCT(INDIRECT()) function. 
Is 
>there a way force this?
>
>I ask because SUM, COUNTIF, and others will accept the 
>range definition instead of the reference to the range.
>
>Of course this won't save me much space, just want it in 
a 
>couple places to make speadsheets a little neater.
>Thanks
>.
>
0
biffinpitt (3171)
2/16/2005 8:23:57 PM
Here is why I'm confused.
If I have data in column A
Then type
"a" in B1
1 in B2
10 in B3
=b1&c1&":"&b1&b3 in b4
I CAN use either
=sum(indirect(b4))
or
sum(indirect(b1&c1&":"&b1&b3))
this also works either way in several other formulas.

I'll assume you're referring to the SUMPRODUCT 
manipulation of INDIRECT (and any others I find that don't 
work ;)
Thanks



>-----Original Message-----
>Hi!
>
>Indirect will not accept arguments that evaluate as 
>formulas. It will only accept arguments that evaluate as 
>references.
>
>Biff
>
>>-----Original Message-----
>>If I have a formula that defines arange in cell A1
>>="column"&"row"&":"&"column"&"row"
>>and another range defined in the same way in B1,
>>I can reference them with
>>=SUMPRODUCT(INDIRECT(A1),INDIRECT(B1))
>>
>>However I can not insert the formula in A1 instead of 
the 
>>reference to A1 in the SUMPRODUCT(INDIRECT()) function. 
>Is 
>>there a way force this?
>>
>>I ask because SUM, COUNTIF, and others will accept the 
>>range definition instead of the reference to the range.
>>
>>Of course this won't save me much space, just want it in 
>a 
>>couple places to make speadsheets a little neater.
>>Thanks
>>.
>>
>.
>
0
hightide (27)
2/16/2005 8:51:13 PM
Reply:

Similar Artilces:

Define range to avoid circular reference
Is there any way to exclude a cell from a range. For example, in Cell F10, I'd like to calculate the sum of all OTHER cells in that row, excluding F10 itself. But SUM(A10:Z10) includes the cell F10, and therefore generates a circular reference. If I were doing a single cell, I could say SUM(A10:E10) + SUM(G10:Z10). However, when I need a similar calculation for all cells in a range, this would be a chore to create these formulas manually for each cell. I think what I need is a range operator to define a range which excludes single cells, something like "A10:Z10 excluding F...

INDIRECT formula not working
Hi, What am I doing wrong here... This formuals does not work: =INDIRECT("'"&"YUNUS"&"-"&A3&"-"&B3&" "&"GD0" &"'!Q1") where A & B are cell references used in the naming of many worksheets I am attempting to obtain the value in Cell Q1 of worksheets: YUNUS-A3-B3 GD0 YUNUS-A4-B4 GD0 YUNUS-A5-B5 GD0 YUNUS-A6-B6 GD0 YUNUS-A7-B7 GD0 YUNUS-A8-B8 GD0 On Thu, 8 Mar 2012 22:27:39 -0000, Yunus <yunus786@btopenworld.com> wrote: > >Hi, > >What am I doing wrong h...

Print range
We have 2 - 8 1/2 x 11 sheets that we put on one worksheet as one page, but print as 2 pages. Is there a way to designate 2 print ranges at one time and print the entire worksheet at one time, as two pages? It is a wide financial document, with multiple columns that extends over more than one page in width. Currently, I set two different print ranges and print the sheet twice. Anything easier? Thanks. You could do your normal print setup one more time and this time Record a Macro while doing it.......then in the future just run the macro and it will do all the stuff for you automatically...

Charting only Used Range with Named Ranges
All right I have been working on this for awhile and I cannot seem to get it perfect. I have a number of charts that take daily security information and chart it as a time series. I am trying to automate this by using named ranges. When I do this the chart's x axis extends much further than the data which is charted. Is there a way to use a countif or sumproduct funtion in the offset formula to only chart the used data? Any workaround would be appreciated. Thanks, Cory -- cooter24 ------------------------------------------------------------------------ cooter24's Profile: htt...

INDIRECT
I would be grateful for any halp with this. I am trying to reference a cell in a different workbook using the INDIRECT function but I am returning #REF. This is the formula I am using : ='S:\Regular Procedural Reports - Test Folder AM\Credit Database 2010 version\[indirect(+M16).xls]rPortfolioAnalysis'!$S$66 Can anyone please help? Many thanks, Andrew The function you'd want to use is =indirect(). But =indirect() won't work if the sending file is closed. Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ or http://xcell05.f...

Overlapping Date Ranges
Hi, I have 4 columns, 2 start date and 2 end dates. In most cases, both are the same. In some, the start date is the same and the end date is different and vice versa, how can I accurately get the number of days overlap? -- I need so much help, but right now, some Excel help will suffice!! Your signature talks about Excel help, but you've posted to a newsgroup related to Access, the database product that's part of Office Professional. If your question really is about Excel, you'd be best off reposting to a newsgroup related to Excel. If it's an Access question, what...

indirect INDIRECT
definitely works. You got it. Thanks. One more problem, which I'll post separately...If I use the Indirect method, I can only be on step removed from the value. In other words, it seems I can't use in the Indirect argument a cell (or combination of cells) that spell the name of the range I want. Example: A1 = b2 b2 = 2 C2= A C3 = 1 A2 is named test Indirect (A2) gives me 2...great Indirect (text(c2,"#")&text(c3,"#")) gives b2, as opposed to 2, which is what I want. Boris =INDIRECT(INDIRECT(C2&C3)) HTH Jason Atlanta, GA >-----Orig...

Copy Range and Assign a Defined Name to the Pasted Range
Hi All, I need some help with the following: I am looking for the code which will allow me to copy a range from one worksheet to another and at the same time assign a defined name to the pasted area. For example, the range A1 to A30 on worksheet 2 is copied and pasted to worksheet 1 starting in cell B30. A defined name of "result" is also created at the same time. The trick is the worksheets will not always be the same and neither will the pasted ranges or the address where the range is to be pasted. Any help would be greatly appreciated. Thanks, Steve Th...

Question indirectly related to Money 2004/2005
Hi all, I've got a question that is indirectly related to Money 2004 onwards. I have designed a software application for recipes that has an interface similar to Money 2004+, in the following ways: 1. UI Layout is the same (ie, Menubar, toolbar, taskpad, main panel, help pane, etc) 2. A tabbed toolbar like Money 2005 is also included 3. Color scheme for the menubar is similar to Money 2004 4. The task-based homepage is the same as in Money 2004, and uses the same color scheme, style and layout. The html and css files of Money were used as a starting/learning point. I would also like to...

How to make a SumIf range not a range ... but a sum of specific cells
=SUMIF(D3,AL3,"=H3+J3+L3+N3+P3+R3+T3+V3+X3+Z3+AB3+AD3") Here is what I am trying to do: If D3 = AL3 then I want it to return the sum of H3+J3+L3+N3+P3+R3+T3+V3+X3+Z3+AB3+AD3 I know it is something simple I am missing ....... Just use If() =if(d3=al3,H3+J3+L3+N3+P3+R3+T3+V3+X3+Z3+AB3+AD3,0) JPD wrote: > =SUMIF(D3,AL3,"=H3+J3+L3+N3+P3+R3+T3+V3+X3+Z3+AB3+AD3") > > Here is what I am trying to do: If D3 = AL3 then I want it to return > the sum of H3+J3+L3+N3+P3+R3+T3+V3+X3+Z3+AB3+AD3 > > I know it is something simple I am missing .........

vlookup combined with INDIRECT
I am using a vlookup to retrieve info from other files (number of hour spent on a specific job number). I am using INDIRECT to specify th RANGE for the VLOOKUP as I want to access many files (employe timesheets). I am using a CONCATENATE to build file names for all the timesheets fo each employee for each time period - this is the cell that the INDIREC function points to for the range of the VLOOKUP. This is close to working for me except that INDIRECT requires al external files to be open othewrwise it returns a #REF! does anyone have any ideas on this -- Wes ---------------------------...

Return range from row number
Hi I have a table from which I need to find specific values based on the rows they reside in. I need to search the (n)th row to return the column number where a specific number (x) is located. Top row of table is G29:CZ29 A1=value to match (x) from another worksheet A2=row to search (n) from another calculation Is there any way to achieve this by converting the known row and its endpoints into a "range" to be inserted in the MATCH function? MATCH(A1,"range",0) e.g. if A2=93, the "range" I need to search is G122:CZ122 Thanks in advance Andy One way: ...

IF(INDIRECT ?
Hi hope you can help! I have a number of copies/versions of the same spreadsheet (layout) which I now have to do a column content count on ie =countif(A1:A100,"A") but I only need to count this if another total in the same column is greater than 0 zero I can do this with an IF statement, but of course when I copy the formula into the other workbooks the original sheet reference remains. I thought I might be able to use INDIRECT() but can't see if this can be tied to an IF() As always any help or other suggestions would be welcome. Regards DonH Wherever you're specify...

Export a Named range to Tab seperated text file
I would like to create a macro to export a named range in Excel to a tab seperated text file, can anyone assist please ? Sub ABC() Dim bk as Workbook, bk1 as Workbook Set bk = ActiveWorkbook Set bk1 = Workbooks.Add(xlWBATWorksheet) bk.Names("MyRange").Copy bk1.Worksheets(1).Range("A1") bk1.SaveAs Filename:="C:\Data\MyFile.txt", _ FileFormat:=xlText, CreateBackup:=False bk1.close SaveChanges:=False End Sub -- Regards, Tom Ogilvy "Peter Kamau" <piquet.pk@gmail.com> wrote in message news:uJXwcohNGHA.2912@tk2msftngp13.phx.gbl... > I would l...

Charting Date Ranges
Trying to chart a series of date ranges (sort of in a timeline-typ fashion). Chart keeps showing beginning and end dates as two separat bars. I need them to span the entire year showing what amount of tim they take up. Where am I going wrong? Eni +---------------------------------------------------------------- | Attachment filename: chart1.jpg |Download attachment: http://www.excelforum.com/attachment.php?postid=366530 +---------------------------------------------------------------- ----------------------------------------------- ~~ Message ...

Selecting a Range
=IF(ISERROR(AVERAGE(O17:O2013)),0,(AVERAGE(O17:O2002))) I have this formula in a cell but I want to add a condition. I want it to average the data in question but only if: c17:c2000="sar" Whats the easiest way to insert that in there? This is an ARRAY formula that must be entered using ctrl+shift+enter AND the ranges must be the same size. Don't use 2000 in one 2002 in another and 2013 in a third =AVERAGE(IF(c17:c2000="sar",o17:o2000)) -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "JimS" <jimx22@msn.com> wrote i...

Comparing a Range of Values
I have named two ranges of cells and I want to compare one range t another to see if they are identicle. I tried =exact(range1,range2 but get an error. Can this be done -- jp ----------------------------------------------------------------------- jpx's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=101 View this thread: http://www.excelforum.com/showthread.php?threadid=48737 try the correl() function. -- Anne Murray "jpx" wrote: > > I have named two ranges of cells and I want to compare one range to > another to see if they are identic...

Formulas SUM(INDIRECT(CONACATENTE( formula
Can anymone explain this formula in excel in simple english. Step b step. I am looking at the imput and output tables. I am looking at th url link with a spreasheet methodology below http://www.statistics.gov.uk/about/methodology_by_theme/inputoutput/downloads/Menu!A You will notice this formula on one of the sheets. Formula =SUM(INDIRECT(CONCATENATE("'",$B$8,"'","!",C$31,$A40,":",C$32,$B40))) I know sum is add up Really don't know its use with the indirect function. Don't understan it. I know the concatenate function is join cel...

Selecting Range issue/ nightmare
Version: 2004 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I get &quot;missing or illegal fields&quot; <br> when I select ranges in Excel. <br> If I have first name, last name, email etc <br> List of 1000. <br> I miss a dozen last names I cannot select the range? <br> I have to have a perfectly filled list every dang cell to select a range? thanks! You don't have to have *any* cells filled to select a range. From the complete lack of descriptive detail about the structure of your data & how you're trying to sele...

Having Excel calculate the number of times a range of values occur
I am wanting to have Excel be able to calculate the number of times that a value within a certain range of values occur in my spreadsheet. For instance: I am looking to see how many times a patient's blood levels of a drug was between 10 - 20. Each time the data is collected it will be a number with one decimal point. Drug level on date x Number of values between 1-10 5 4 10 22 15.1 8 9.3 10.2 18.3 25 36 Thanks for any help you can offer. -- Rodney Hi Rodney, Am Fri, 16 Oct 2009 15:56...

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 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 > &g...

Range updates
is it possible to create a formula so it adjust automically to number of row or columns in a range, for example if there were values in the range C1 to C5 my countif formula would automatically adjust to countif(c1:c5) and if there values in cells c1 to c1000 the formula would adjust to (c1:c1000) Thanks Not without code, but why not just make it c1:c5000 ? -- -John http://www.jmbundy.blogspot.com/ Please rate when your question is answered to help us and others know what is helpful. "nir020" wrote: > is it possible to create a formula so it adjust automi...

Range compariosn
I have multiple sets of ranges (10) and I would like to show the best of. Currently I have nested if statements to give me the "best of", but I was wondering if there was another/better way of doing this? Any Suggestions? This is what the ranges look like Group A rate 15 30 45 60 5 100 101 102 103 5.125 101 102 103 104 5.250 103 104 105 106 AVG(x) x x x x Group B rate 15 30 45 60 5 101 102 103 104 5.125 102 103 104 105 5.250 104 105 106 107 AVG(x) x x x x x = ...

Selecting Dynamic Data Range
Hello Everyone, I have been searching for awhile now to find an answer..but with n luck. My question: I have 2 years of data broken down by month (columns) and manufacturin facility (2-9 rows)..each cell represents a revenue. My boss has aske me to give him the ability to set the start month and end month an only that data is shown.... Here is how it is laid out: Jan 04|Feb04|Mar04|Apr04|etc... Fab1 Fab2 Fab3 Fab4 Fab5 Fab6 Fab7 I use a pivot table to get this format, since the initial data is muc more complicated...any ideas how I could provide something so if m boss only wants to see...

Indirect references with VLOOKUP
RE: Excel 2007 I have a workbook, with a SUMMARY worksheet (tab), where the first column (A) contains stock ticker symbole, e.g. XOM. In addition, there are many more worksheets (tabs), all labelled in this way 091231, 100131, etc That is, YYMMDD. In all those pages I have a I have an area A2:G50, where the first column contains a stock ticker symbol, and columns B, C, etc. have various data. In the SUMMARY worksheet I want to callup data from the other sheets. For example, say SUMMARY!A4 is XON I could have =VLOOKUP(A4,'091231'!A2:G50,2,1) and that would return...