Help Modifying this Formula ....

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mhz - 27 Jul 2006 09:55 GMT

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.

Reply to this Message

OZDOC - 27 Jul 2006 11:44 GMT

(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

> hi, I am trying to modify the formula below to use the LEFT function..
> any help Appreciated..
[quoted text clipped - 6 lines]
>
> Thanks In Advance..

Reply to this Message

OZDOC - 27 Jul 2006 11:47 GMT

(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

> hi, I am trying to modify the formula below to use the LEFT function..
> any help Appreciated..
[quoted text clipped - 6 lines]
>
> Thanks In Advance..

Reply to this Message

OZDOC - 27 Jul 2006 11:47 GMT

(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

> hi, I am trying to modify the formula below to use the LEFT function..
> any help Appreciated..
[quoted text clipped - 6 lines]
>
> Thanks In Advance..

Reply to this Message

Mhz - 28 Jul 2006 06:42 GMT

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

Signature

Mhz

Reply to this Message

Bob Phillips - 28 Jul 2006 16:42 GMT

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.

Signature

HTH

Bob Phillips

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

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

Reply to this Message

Mhz - 29 Jul 2006 13:14 GMT

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

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

Signature

Mhz

Reply to this Message

Mhz - 30 Jul 2006 07:48 GMT

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

Reply to this Message

Bob Phillips - 30 Jul 2006 11:33 GMT

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

Signature

HTH

Bob Phillips

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

> I Found a slight workaround for the Formula I presented wanting to use
> the LEFT Function...
[quoted text clipped - 5 lines]
>
> Thanks for the assistance on this topic..;)

Reply to this Message