Array Formula Help

[Dave E]

Hello

I am half-way there with an array formula (first time!) but I'm struggling with the nitty gritty... wonder if anyone can help do what I need...

(I've simplified the formala down to just a few columns etc - made up the scenario).

Col A

Row 1: W London
Row 2: C London
Row 3: Liverpool
Row 4: W London
Row 5: W London
Row 6: Birmingham

Col B

Row 1: Mark
Row 2: Mark
Row 3: John
Row 4: Mark
Row 5: Mark
Row 6: Keith

Col C

Row 1: 10
Row 2:
Row 3:
Row 4:
Row 5: 20
Row 6: 23

I am trying to average the values in Col C, when Col A contains "London", and Col B = Mark. This is what I have so far:

=AVERAGE((A1:A6="W London")*(B1:B6="Mark")*(C1:C6))

There are 2 problems with this:

1) It only picks up "W London"... I need it to pick up anything with "London" in the string.

2) The average should be 15 (with just W London), but it picks up row 4 as a 0, and averages to 10. I need to exclude the blank cells from the average.

I've tried various ways around, like including <>"" clauses etc, but they are probably miles off :-O

Cheers in advance for any help...

Dave E

 

[Stormseed]

PERFECT !!! Thank you so much, PGC !!!

I should admit that everything goes into my head provided the latter finds a way to get in