Average(If( with multiple criteria including a letter

[wakisme]

Hi
Any help would be appreciated.

The part of the formula I have written below works in another workbook if I am checking for the number 2
AVERAGE(IF((INDIRECT($U$5): (INDIRECT($W$5))=A6)*((OFFSET(INDIRECT($U$5);0;-2)): (OFFSET(INDIRECT($W$5);0;-2))=2)

But in a new workbook I need to check for L not 2

The cells I am searching could contain
L01 so I need True
L02 so I need True
126 so I need False
L167 so I need True

The complete formula is searching for 2 criteria and returns the average of the last 6 rows from Column Z that meet those criteria. It works when I am searching for 2

I don't know why I have a grimace face instead of a colon.
Thanks a lot

[AliGW]

Try typing a closing bracket and a colon and you’ll find out.

[wakisme]

Hi Ali
Thanks I can now see smilies on
Could you advise if I am able to turn smilies off for this post so I can add my entire formula.

[wakisme]

Hi
I think I have sorted this problem with a 'helper' cell but I will still appreciate any alternative solutions.
In the 'helper' cell U10 I have the Text L01
New formula that appears to be working is
AVERAGE(IF((INDIRECT($U$5): (INDIRECT($W$5))=A6)*((OFFSET(INDIRECT($U$5);0;-2)): (OFFSET(INDIRECT($W$5);0;-2))>=U10)
Thanks

[AliGW]

Just add spaces to the formula to remove the smilies:

=AVERAGE(IF((INDIRECT($U$5)INDIRECT($W$5) ) =A6)*((OFFSET(INDIRECT($U$5);0;-2) ) OFFSET(INDIRECT($W$5);0;-2))>=U10)

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.