Hi trying to understand this formula can someone please break it down....thanx
=SUMIF(INDIRECT("WEEK"&COLUMNS($B$6:B7)&"!C7:L7"),200+ROWS($5:6)-1, INDIRECT("WEEK"&COLUMNS($B$6:B7)&"!C298:L298"))
Hi trying to understand this formula can someone please break it down....thanx
=SUMIF(INDIRECT("WEEK"&COLUMNS($B$6:B7)&"!C7:L7"),200+ROWS($5:6)-1, INDIRECT("WEEK"&COLUMNS($B$6:B7)&"!C298:L298"))
Hi!
Translated:
=SUMIF(Week2!C7:L7,201,Week2!C298:L298)
The Indirect function is being used to "build" the references to the
particular sheets. The criteria is also being "built" by using the Rows
function.
When this formula is drag copied across the sheet name increments to Week3,
Week4, etc and when drag copied down the criteria increments to 202, 203,
etc.
Biff
"Grimzby" <[email protected]> wrote in
message news:[email protected]...
>
> Hi trying to understand this formula can someone please break it
> down....thanx
> =SUMIF(INDIRECT("WEEK"&COLUMNS($B$6:B7)&"!C7:L7"),200+ROWS($5:6)-1,
> INDIRECT("WEEK"&COLUMNS($B$6:B7)&"!C298:L298"))
>
>
> --
> Grimzby
> ------------------------------------------------------------------------
> Grimzby's Profile:
> http://www.excelforum.com/member.php...o&userid=23905
> View this thread: http://www.excelforum.com/showthread...hreadid=501910
>
Ooops!
> Translated:
> =SUMIF(Week2!C7:L7,201,Week2!C298:L298)
Should be:
=SUMIF(Week1!C7:L7,201,Week1!C298:L298)
Biff
"Biff" <[email protected]> wrote in message
news:[email protected]...
> Hi!
>
> Translated:
>
> =SUMIF(Week2!C7:L7,201,Week2!C298:L298)
>
> The Indirect function is being used to "build" the references to the
> particular sheets. The criteria is also being "built" by using the Rows
> function.
>
> When this formula is drag copied across the sheet name increments to
> Week3, Week4, etc and when drag copied down the criteria increments to
> 202, 203, etc.
>
> Biff
>
> "Grimzby" <[email protected]> wrote in
> message news:[email protected]...
>>
>> Hi trying to understand this formula can someone please break it
>> down....thanx
>> =SUMIF(INDIRECT("WEEK"&COLUMNS($B$6:B7)&"!C7:L7"),200+ROWS($5:6)-1,
>> INDIRECT("WEEK"&COLUMNS($B$6:B7)&"!C298:L298"))
>>
>>
>> --
>> Grimzby
>> ------------------------------------------------------------------------
>> Grimzby's Profile:
>> http://www.excelforum.com/member.php...o&userid=23905
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=501910
>>
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks