Help Modifying this Formula ....

hi, I am trying to modify the formula below to use the LEFT function.. any help Appreciated..

=SUM(COUNTIF(INDIRECT("DAY"&ROW(INDIRECT("1:31"))&"!H6:H35"),"=W"))

I tried this below, but I get a reference error:

=SUM(COUNTIF(INDIRECT("DAY"&ROW(INDIRECT("1:31"))&"!(LEFT(H6:H35,1"),"=W"))

Thanks In Advance..

(LEFT(H6:H35,1")

I cant look it up now but is left used in a cell reference ? if so you have
a range and I don't think it will work in the range as its cell specific and
would be the problem

"Mhz" <[email protected]> wrote in message
news:[email protected]...
>
> hi, I am trying to modify the formula below to use the LEFT function..
> any help Appreciated..
>
> =SUM(COUNTIF(INDIRECT("DAY"&ROW(INDIRECT("1:31"))&"!H6:H35"),"=W"))
>
> I tried this below, but I get a reference error:
>
> =SUM(COUNTIF(INDIRECT("DAY"&ROW(INDIRECT("1:31"))&"!(LEFT(H6:H35,1"),"=W"))
>
> Thanks In Advance..
>
>
> --
> Mhz
> ------------------------------------------------------------------------
> Mhz's Profile:
> http://www.excelforum.com/member.php...o&userid=35980
> View this thread: http://www.excelforum.com/showthread...hreadid=565502
>

(LEFT(H6:H35,1")

I cant look it up now but is left used in a cell reference ? if so you have
a range and I don't think it will work in the range as its cell specific and
would be the problem

"Mhz" <[email protected]> wrote in message
news:[email protected]...
>
> hi, I am trying to modify the formula below to use the LEFT function..
> any help Appreciated..
>
> =SUM(COUNTIF(INDIRECT("DAY"&ROW(INDIRECT("1:31"))&"!H6:H35"),"=W"))
>
> I tried this below, but I get a reference error:
>
> =SUM(COUNTIF(INDIRECT("DAY"&ROW(INDIRECT("1:31"))&"!(LEFT(H6:H35,1"),"=W"))
>
> Thanks In Advance..
>
>
> --
> Mhz
> ------------------------------------------------------------------------
> Mhz's Profile:
> http://www.excelforum.com/member.php...o&userid=35980
> View this thread: http://www.excelforum.com/showthread...hreadid=565502
>

(LEFT(H6:H35,1")

I cant look it up now but is left used in a cell reference ? if so you have
a range and I don't think it will work in the range as its cell specific and
would be the problem

"Mhz" <[email protected]> wrote in message
news:[email protected]...
>
> hi, I am trying to modify the formula below to use the LEFT function..
> any help Appreciated..
>
> =SUM(COUNTIF(INDIRECT("DAY"&ROW(INDIRECT("1:31"))&"!H6:H35"),"=W"))
>
> I tried this below, but I get a reference error:
>
> =SUM(COUNTIF(INDIRECT("DAY"&ROW(INDIRECT("1:31"))&"!(LEFT(H6:H35,1"),"=W"))
>
> Thanks In Advance..
>
>
> --
> Mhz
> ------------------------------------------------------------------------
> Mhz's Profile:
> http://www.excelforum.com/member.php...o&userid=35980
> View this thread: http://www.excelforum.com/showthread...hreadid=565502
>

Could you elaborate on the LEFT Function Not being possible in this Formula? Thanks

The problem is that LEFT will not work in COUNTIF, so that is where you
incorporate it in SUMPRODUCT so as to return an array of results for SP to
process. However, the INDIRECT has to be used to get a 3D calc, but that
doesn't seem to work with LEFT. I haven't quite figured out why yet, if and
when I do, it should be clear whether or not there is an alternative
solution.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Mhz" <[email protected]> wrote in message
news:[email protected]...
>
> Could you elaborate on the LEFT Function Not being possible in this
> Formula? Thanks
>
>
> --
> Mhz
> ------------------------------------------------------------------------
> Mhz's Profile:
http://www.excelforum.com/member.php...o&userid=35980
> View this thread: http://www.excelforum.com/showthread...hreadid=565502
>

The problem is that LEFT will not work in COUNTIF, so that is where you
incorporate it in SUMPRODUCT so as to return an array of results for SP to
process. However, the INDIRECT has to be used to get a 3D calc, but that
doesn't seem to work with LEFT. I haven't quite figured out why yet, if and
when I do, it should be clear whether or not there is an alternative
solution.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Mhz" <[email protected]> wrote in message
news:[email protected]...
>
> Could you elaborate on the LEFT Function Not being possible in this
> Formula? Thanks
>
>
> --
> Mhz
> ------------------------------------------------------------------------
> Mhz's Profile:
http://www.excelforum.com/member.php...o&userid=35980
> View this thread: http://www.excelforum.com/showthread...hreadid=565502
>

Once again, Thanks Bob. I will just use another formula method for checking the LEFT positions on all sheets being tested... At least until there's a better future way of doing it as you mentioned...

Countif ... Just Hate Those LEFTs or Vise Versa .lol....

of course, should have thought of that myself. Wood for the trees syndrome.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Mhz" <[email protected]> wrote in message
news:[email protected]...
>
> I Found a slight workaround for the Formula I presented wanting to use
> the LEFT Function...
>
> =SUM(COUNTIF(INDIRECT("DAY"&ROW(INDIRECT("1:31"))&"!G6:G35"),"GI*"))
>
> Simply Counting Anything within the G6:G35 Range that Begins With "GI"
> This Works for me just as well as using the LEFT Function...
>
> Thanks for the assistance on this topic..
>
>
> --
> Mhz
> ------------------------------------------------------------------------
> Mhz's Profile:
http://www.excelforum.com/member.php...o&userid=35980
> View this thread: http://www.excelforum.com/showthread...hreadid=565502
>