Help Modifying this Formula ....

[Mhz]

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.

 

[OZDOC]

(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

 

[OZDOC]

(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

 

[OZDOC]

(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]

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

 

[Bob Phillips]

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]

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]

I Found a slight workaround for the Formula I presented wanting to us
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..;

 

[Bob Phillips]

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)