# SUMPRODUCT With Indirect Formula

Jul 15, 2013

I am trying to sum the same data across several tabs but I am using information in the column and row headers to find it. For some reason this formula wont work and I can't figure out what I'm doing wrong.

I'm getting #REF! and when I step through it, it fails when it reaches the Indirect statement.

## SUMPRODUCT INDIRECT & SUMIF Formula

Jul 12, 2006

I have a set of data as attached where the productivity of each staff is recorded on a daily basis.However, the cell in which their name is located on each tab of the day of the month might not be the same. I am thankful that the forum actually provided me with the formula located in cell c15.

1)However, I would appreciate if someone could translate what the formula means as I am not so sure what the formula represents.

2)The reason is I would like to calculate how many training days (TR), Medical Leave(ML) and Emergency Leave(EL) for the month of January for each staff.I tried modifying the formula but it didnt work.

3) Is it possible to use a vlookup function for this solution?

## Sumproduct With An Indirect?

Nov 16, 2009

The following formula sorts for specifics in the sheet named 200910 in the specified ranges in columns A and D to return a total found in column AB. This works just fine.

What I am looking to do, instead of telling excel what sheet to go to, is insert this: =INDIRECT(TEXT(Y10,"yyymm")&"!ab1749") to find the matching sheet name to the date that resides in cell Y10.

These both work separately on their own to return the needed value. How do I put them into one formula without telling excel what sheet to go to (1st formula) and specifically what cell to go to (2nd formula) because the cell location may change and I want to completely automate this?

## Sumproduct & Indirect

Apr 20, 2007

=SUMPRODUCT(--(A2:A1000=B1))

Column A has got random numbers, my formula attemts to capture the count of numbers as specified in B1, is there a way I can nest an indired t formula to mention in cell C1 ( > or < ) so it can look for numbers greater than the number in B1 or lesser as indicated

## Sumproduct & Indirect Functions

Jul 15, 2006

Can someone help with this formula,

Cell \$A\$24 = A cell formatted as Month and Year = July06
Cell \$B\$1 = a date 1/7/06 linked to \$A\$24

Trying to use the indirect function to ref a sheet called July06 and other ranges here a example of one range =July06!\$D\$2:\$D\$247

This is what I've got

=SUMPRODUCT(--(INDIRECT(TEXT(\$A\$24,"mmmmyy")&"!\$D\$2:\$D\$247<="&\$B\$1)*(INDIRECT(TEXT(\$A\$24,"mmmmyy")&"!\$Y\$2:\$Y\$247>= "&\$B\$1)*(INDIRECT(TEXT(\$A\$24,"mmmmyy")&"!\$C\$2:\$C\$247="&\$A2)))))

## How To Use Indirect Function Within Sumproduct

Dec 29, 2009

I have the following formula, which works; however I need to make it dynamic.

## INDIRECT In SUMPRODUCT For Sheet Name

Dec 18, 2006

In book3.xls, worksheet 'statistics_by_class_day', how can I rewrite the formula in C7 such that the count is based on number chosen in A3 and C3?

=SUMPRODUCT((INDIRECT("'attendance"&\$A\$3&"'!C:C")=\$A\$7)*((INDIRECT("'attendance"&\$A\$3&"'!I:I")<>0)))

In the formula, I:I refer to Day 4 of worksheet attendance9 (ie Column I), can I use INDIRECT() by referring to C3?

## SUMPRODUCT / INDIRECT Functions Not Working With ROW Reference When Combined

Apr 4, 2014

Basically I have an equation:

=SUMPRODUCT(G9:G11,H9:H11)/SUM(G9:G11)

It's just a simple percentage calculator for my purposes and works fine as is. However, I want to make it dynamic whereby from a user input the length of the array will increase or decrease, e.g. G9-G11 will become G9-G12 if there are four rows occupied with data. For all my other SUMming equations I have solved this and even came up with what I think is a perfectly valid solution for this one. The one problem is that it doesn't work when combined into a single equation.

My solution for the upper part of the fraction is this:

"A1"=SUMPRODUCT(INDIRECT("G9:G"&MIN(ROW(G9)+A8-1)),INDIRECT("H9:H"&MIN(ROW(H9)+A8-1)))

Where the user inputs the number of rows used in A8.

The solution for the lower part of the fraction is this:
"A2"=SUM(INDIRECT("G9:G"&ROW(G9)+A8-1))

[code]....

## INDIRECT Not Working For Dynamic Named Range In SUMPRODUCT

Feb 11, 2012

The formula is =SUMPRODUCT((Group="A")*(Project_Description="Long Term")*(Profile="B")*(INDIRECT(B9)="x"))

B9 through M9 have header text Jan_2012, Feb_2012...Dec_2012. Basically my aim is to get the above formula working before I drag it across so that the named ranges get picked up automatically from the headers.

Jan_2012 thru Dec_2012 are dynamic named ranges using INDEX (and not OFFSET as someone mentioned OFFSET is a volatile function).

The above SUMPRODUCT formula is giving me a #REF! error for the (INDIRECT(B9)="x") part. I know that because when I replace it with (Jan_2012="x") it works fine.

I realized while writing this that it may be because the named range in Jan_2012 is not the same size as that of other arrays. [but it is the same size - I've re-confirmed just now]

## Develop An Indirect Indirect Validation Drop Down List

Aug 18, 2009

I am trying to develop an Indirect Indirect Validation drop down list. Example, Building - Floor - Room, i.e. Select Building from a Validation drop down list. Then based upon the Building selected, select only the Floors applicable to the Building Selected. I am able to achieve this via an Indirect Validation drop down. However, when I attempt to then select the Rooms applicable to the Floor of the Building I selected, I can not produce an Indirect Validation off a previous Indirect Validation.

In the attachment, I have used Plant - Location - Room. I have name ranged the selections, and have used Validations Lists for Plant, and Indirect Validations for Location. The error occurs where I attempt to do an Indirect Validation for Room.

## Copying Down An Indirect Formula

Aug 20, 2009

How can I copy down an indirect formula? When I copy it the lookup reference doesn't change. My formula is: =IF(INDIRECT("Q1")="",INDIRECT("R1"),INDIRECT("Q1"))

but when I copy down the cell reference stays the same (I need to keep the indirect formula because I'm adding columns in column Q but it needs to reference column Q even when columns are added). From reading through some other posts I believe I need to add a ROW() or COLUMN() formula in there somewhere.

## Array Formula And Indirect

Aug 27, 2007

getting an array formula to work with my indirects....

## Copy Down An INDIRECT Formula

Sep 18, 2007

When I try to copy this formula down, it stays the same and won't reference the cells.

=INDIRECT("PO_Order_Form!B5")

## #VALUE Error In Indirect Formula

Sep 24, 2008

rate2008
rate2007
...

And I want to show this data in a table, where I have the years 2008, 2007, ... in cells A1:A10 and the formulas =INDIRECT("rate"&A1), =INDIRECT("rate"&A2), ... in cells B1:B10.

For some reason, I am getting nothing but #VALUE! errors in my indirect formulas. In fact, even if I take out the indirect and just have ="rate"&A1, ="rate"&A2, etc., I still get the errors. It seems like the problem is with the & operator. This only seems to be a problem in this certain workbook; I am able to get the desired results if I open a new workbook.

## #REF! And #VALUE! Error With INDIRECT Formula

Jul 25, 2006

when using the following forula as below; =INDIRECT(INDEX(\$B\$37:\$B\$62,\$B\$3)&"!"&ADDRESS(ROW(D6),COLUMN(D6))). centain cells come up with #REF! and or #VALUE!

## Add Indirect Formula Via Macro

Oct 26, 2006

I need to create a formula for a series of ranges that have a variable sheet name (which is located on sheet Backend!E15) and when it creates the formula will reference the exact same cell on the variable sheet.
this is what i have so far...

Option Explicit

Sub formulaset()
Dim Cell As Range
Dim target As String
For Each Cell In Range("b4:al132")
Application. ScreenUpdating = False
Cell.FormulaR1C1 = "=INDIRECT(CONCATENATE(BackEnd!E15,""!"",target))"
Application.ScreenUpdating = True
Next Cell
End Sub

but this is the answer I am getting in the first cell of the range...

=INDIRECT(CONCATENATE(BackEnd! 'E15',"!",target))

as you can see I am having trouble getting the target address to lock in. To make things worse, its needs to be in " " so the concatenate creates the corect address link.

## Alternate Formula For Sum Indirect

Feb 14, 2007

I have tried to apply '= SUM(INDIRECT("A2:A10"))' formula to do the SUM at cell A11. But, if I add two more Rows, then my formula moves down to cell A13 but numbers in Cell A11 and A12 does not get added to the total. How can I avoid that? I have reserached this site extensively and could not find an archived solution.

## Formula To Increment Indirect References

Mar 5, 2009

I have a sheet which needs to look up one reference and then fill a table with the rest of them.

EG:

Cell A1 contains '0091 911'!\$E\$2 (cell E2 contains value 100)

Cell A2 contains =indirect(A1) and displays value 100

I need a formula which will auto fill the remaining cells in the table.

eg:

Cell A3 fills to contain '0091 911'!\$E\$3 (row +1)

Cell B2 fills to contain '0091 911'!\$F\$2 (column +1)

so it needs to fill the Indirect reference and not =indirect(A1),=indirect(A2)....

## 'INDIRECT' Formula Across Excel Versions

May 22, 2009

I have made a file that works perfectly in excel 2007, but when I send it to a client it doesn't work as they have 2003.

Nov 7, 2012

Although I have seen lots of formulae to total a dynamic column, I found a brilliant formula on the Internet, as follows

HTML Code:

=SUM(OFFSET(INDIRECT("R1C",),,,ROW() -1))

The problem is I cannot work out how to adapt it to total across rows (which will expand with new columns).

## Indirect Function In Picture Formula

Mar 31, 2007

I'm in the middle of making a yahtzee game in excel. Does anyone know if it is possible to use the indirect function in the formula of a picture. I have embedded 6 bitmap images of dice on sheet 2 (worksheet name: "Dice Images"). I took a picture of one of those dice and pasted that picture on sheet1. The formula in the picture is: ='Dice Images'!\$A\$1

This means that it is refering to cell A1 of the "Dice Images" worksheet of which contains the pasted bitmap of the dice image. I was wondering if I could use the INDIRECT function instead of the formula above but the following returns an error:
=INDIRET("'Dice Images'!\$A\$2",true). It returns the following error: "The text you entered is not a valid reference or defined name". It doesn't seem to like the INDIRECT function for some reason, unless I am doing something wrong.

## Indirect Formula For Sheet Name & Range

Jan 10, 2008

I have a formula: ='Jan 7-11'!D10. I want to replace the tab name 'Jan 7-11' to change based on the content of F6 (which is a data validation for all tabs in my workbook).

## Relative Indirect Formula To SUM Range

Feb 13, 2008

I am using VB in Excel to write a formula for summing a column of numbers that will vary in length. I have been using the Indirect formula and have the start and end cells of the range identified in another location. My problem is, the macro in vb is sending the Indirect formula to the cells containing the start and end points by using relative functions ie. go across 30 columns and up 10 rows to get start cell. I wanted it to just save the cell location of the start and end points.
EG.

VB formula is: "= SUM(INDIRECT(R[-23]C[27]&R3C29):INDIRECT(R[-23]C[27]&R3C30))"
whereas I just want it to read "=Sum(Indirect(C4&D5):INDIRECT(C4&D6))"

where cell C4 gives the column letter and D5 the row number of the start of the range and C4 and D6 give the column letter and row number of the end of the range.

## Non Volatile Replacement For INDIRECT In SUMIFS Formula

Dec 4, 2013

Okay so I'm trying to us the SUMIFS formula to add values on a different sheet based on criteria on that sheet but I want to use a text string on the sheet with the SUMIFS formula to reference the sheet with the data on it.

My original formula was:

=SUMIFS(INDIRECT("'"&A2&"'!K4:K500"),INDIRECT("'"&\$A\$2&"'!B4:B500"),"=LEFT(AA1,2)",LEFT(INDIRECT("'"&\$A\$2&"'!C4:C500"),3),"=MID(AA1,7,3)")

However I kept getting an error and excel said that INDIRECT("'"&A2&"'!K4:K500") was volatile, so I tried to replace it with INDEX and my formula was:

=SUMIFS(INDEX(A2:A2,1)&"'!K4:K500",INDEX(A2:A2,1)&"'!B4:B500",LEFT(AA1,2),LEFT(INDEX(A2:A2,1)&"'!C5:C500",3),MID(AA1,7,3))

However INDEX(A2:A2,1)&"'!K4:K500" returns the value "1251062 EP.EL+CB-A10'!K4:K500" which wont calculate properly because of the quotation marks but excel is auto inserting them.

## Sheet Cell Reference And INDIRECT Formula

Jun 9, 2009

I have this formula in excel 2007:

Blad is Dutch for sheet, by the way.

In this formula, I want to let the ranges in Sheet1 be dependent of values in Sheet2. The formula itself is in Sheet2.

W must be replaced by the value of Sheet2!B12 and X to be replaced by the value of Sheet2!B9.

I tried this by using the INDIRECT formula, but the quotation marks of the search values are giving errors.

## Dynamic Cell Reference In Indirect Formula

Oct 11, 2011

I need making a dynamic cell reference in an indirect formula, which looks at a different workbook.

The other workbook is called: ESF.xlsx
The sheet name in the other workbook is defined in Cell B2
The first cell i need to pull across is B115.

In cell B8, This is what i have so far:

=INDIRECT("[ESF.xlsx]"&B2&"!"&"B115")

How do i make it so that i can copy this formula quickly, but so only the row and column reference change?

Like in C8 i would want it to look at cell C115, In C9, id want it to look at C116 etc.

## Changing Indirect Reference When Dragging Formula

Dec 9, 2012

I have been using the following formula to do conditional summing over multiple worksheets:

=SUMPRODUCT(SUMIF(INDIRECT("'"&\$AB\$2:\$AB\$13&"'!b3:b39"),\$B16,INDIRECT("'"&\$AB\$2:\$AB\$13&"'!c3:c39")))

The formula works fine, but when I drag it to the next column c3:c39 stays the same. How do I get that reference to change when I drag the formula over multiple columns.

## Indirect Function Placement In Array Formula

Feb 27, 2014

The following formula is all on one line. I want to do indirect for file name and tab in all instances it shows up. (I want to have the filename in one cell and sheet/tab name in another cell to reference the indirect to.)

How would I use indirect in the formula to reference the cells?

## Using Indirect Formula To Pull In Data From Different Worksheet

Jul 7, 2014

I am trying to use the indirect formula to pull in data from a cell in a different worksheet using the tab name as my reference. For example:

I have my tab name in cell A2, then I am using this formula to pull the information from cell g29 on the tab listed in A2, but its not working:

=INDIRECT(A2&”!”&”g29″)

Also, my tab name in cell a2 is a date, does that make any difference? I've tried a couple variations on the indirect formula and have gone from the #REF! error to the #NAME? error.

## Variable Cell Reference In INDIRECT Formula

Sep 28, 2006

I want to create a formula of the form: =INDIRECT(ADDRESS(4,2,2,TRUE,(\$A9) and insert this in a cell BUT the \$A9 reference needs to reference the row of the cell where the formula is being inserted which will vary. ie. A30, A31, etc. How do I do this?