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" wrote in message
...

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" wrote in message
...

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" wrote in message
...

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


--
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" wrote in message
...

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....


--
Mhz
------------------------------------------------------------------------
Mhz's Profile: http://www.excelforum.com/member.php...o&userid=35980
View this thread: http://www.excelforum.com/showthread...hreadid=565502

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

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" wrote in message
...

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