Average of A if column B is blank and C is populated unless containing specific text

Hello all,

I've had a look through the forum for the answer to this, but have not managed to locate it. If this has been asked in another post, please could you direct me to it.

I have created an example to demonstrate what I am trying to achieve (in plain text below and attached as an XLSX).

I am looking to work out the average of column A (Time) if the following criteria is matched:

1. Column B (Fruit) is blank AND
2. Column C (Colour) is populated, unless
a) Column C contains Green OR
b) Column C contains Yellow OR
c) Column D contains Large.

A B C D
1 TIME FRUIT COLOUR SIZE
2 6 APPLE GREEN LARGE
3 4 MEDIUM
4 9 ORANGE ORANGE SMALL
5 18 ORANGE SMALL
6 17 APPLE BLUE MEDIUM
7 11 ORANGE MEDIUM
8 23 GREEN SMALL
9 6 ORANGE SMALL
10 3 BLUE LARGE
11 7 BANANA MEDIUM


Using this example, only cells A5 and A9 meet the criteria specified, so the average should be 6.

In future, the amount of rows may change and additional colours added, so I would like my formula to handle these.

I am currently using Office 2007 but have access to any version.

Any help you can offer would be appreciated hugely.

Kind regards,
Matt

P.S. Sorry for the poor formatting of the plain text example - I will learn how to use the insert table function for next time!

Hi mattr86,

But cell D5 is not "Large" , please check.

Regards,
DILIPandey
<click on below * if this helps>

but it should not count A if C is Green or Yellow, or D is Large.

Okay.. in that case D10 is " Large " .. and you are considering that row.


Regards,
DILIPandey
<click on below * if this helps>

I got the point why we are confused.. your serial numbers in column A looks like ROWs... because of this A5 looks like 5 but that is actually 4

Would be great if you could put some different values there and then we can try cracking this.. thanks.


Regards,
DILIPandey
<click on below * if this helps>

Hi Matt,

see the below file where I am getting 3 eligible values..

Formula:

Please Login or Register  to view this content.

Enter above formula using ctrl shift enter key combination

Example2.xlsx

Regards,
DILIPandey
<click on below * if this helps>

Okay... got it now, use below one:-

Formula:

Please Login or Register  to view this content.

See attached:- Example2(1).xlsx


Regards,
DILIPandey
<click on below * if this helps>

you are welcome Matt...

Cheers

Regards,
DILIPandey
<click on below * if this helps>