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" <Grimzby.21reba_1137468001.8071@excelforum-nospam.com> wrote in
message news:Grimzby.21reba_1137468001.8071@excelforum-nospam.com...
>
> 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" <biffinpitt@comcast.net> wrote in message
news:e0cX1dxGGHA.2012@TK2MSFTNGP14.phx.gbl...
> 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" <Grimzby.21reba_1137468001.8071@excelforum-nospam.com> wrote in
> message news:Grimzby.21reba_1137468001.8071@excelforum-nospam.com...
>>
>> 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