Indirect function combined with Array

**NYRealEstateAnalyst** · 11-11-2010, 12:23 PM

I can't figure out why this function doesn't work. I get a "#VALUE!"

={SUM((MONTH($B$3)=MONTH(INDIRECT("'"&I$15&"'!F14:IV14")))*(YEAR($B$3)=YEAR(INDIRECT("'"&I$15&"'!F14:IV14")))*INDIRECT("'"&I$15&"'!F"&ROW()&":IV"&ROW()))}

The reason for using the array formula instead of a sumif is because I'm trying to dummy proof the formula so that no matter what day of the month the user uses, it still works. Then, my "&I$15&" is looking for the right tab from which to pull data.

The more basic function below works but I want to be able to copy the fomula down the column which is the reason for the row() formula. Additionally, if the user adds a row, I want the formula to be be copied easily.

={SUM((MONTH($B$3)=MONTH(INDIRECT("'"&I$15&"'!F14:IV14")))*(YEAR($B$3)=YEAR(INDIRECT("'"&I$15&"'!F14:IV14")))*INDIRECT("'"&I$15&"'!F18:IV18"))}

The function below works too so I don't understand why I can't combine this with the function immediately above to make the first function work.
={SUM(INDIRECT("'"&I$15&"'!F"&ROW()&":IV"&ROW()))}

**DonkeyOte** · 11-11-2010, 12:36 PM

Use of ROW will be your issue - if you revert to ROWS - ie ROWS(F$1:F18) where F18 is first formula cell then it should work.

Regards use of Array in first instance...

If the header dates (row 14) are always 1st of month a SUMIF should suffice - you can adjust B3 as part of the calculation, eg:

Please Login or Register  to view this content.

Note I used R1C1 notation in the INDIRECT for ease of reference.

**NYRealEstateAnalyst** · 11-11-2010, 01:41 PM

Thanks! That was it. I still don't understand WHY Excel won't let me use the ROW function... but your solution worked perfectly! Thanks again!

**DonkeyOte** · 11-11-2010, 01:46 PM

ROW returns an Array whereas ROWS does not.

Assume A1 to be active row:

ROW() evaluates to {1}

ROWS(A$1:A1) evaluates to 1

**Eastbay2** · 05-09-2013, 05:36 PM

I also could use some help. I’ve never used the "Indirect" formula before and I’m struggling to write this into my current formula. I have an array formula and I’m trying to make it more dynamic by having the formula select the correct tab (Tabs are labeled by Day of the week) to pull the data from. Here is my current formula:

{=SUM(IF(Sunday!$A$7:$A$4954=$A7,IF(Sunday!E$7:E$4954=0,1,0),0))/COUNTIF(Sunday!$A$7:$A$4954,$A7)}

I’m trying to my the tab reference (“Sunday”) dynamic. I’ve tried adding the indirect to the formula with no success. Please help.

{=SUM(IF(INDIRECT($A$4&"!$A$7:$A$4954=$A7"),IF(INDIRECT($A$4&"!E$7:E$4954=0,1,0),0))")/COUNTIF(INDIRECT($A$4&"!$A$7:$A$4954,$A7)"}

Thanks!

**arlu1201** · 05-10-2013, 02:46 AM

Eastbay2,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.