IF ARRAY and MATCH for criteria met over several rows and columns to return yes or no

[Ulli8]

Hi there,

I would like the following SUMMARY sheet (sheet1) to count on a different tab if all items with an 8 in column B have a 'Y' next to them in column C, and return 'Yes', if so, and return 'No' if not. I think it needs to be an IF function with a MATCH over an ARRAY, but I am stuck.

Sheet1
Row	A	B	C
1	8	Water Pressure - Consistently good water pressure throughout apartment	If all rows that have 8 (a1) in column B on sheet 2 also have a 'Y' in column C on sheet 2 then return 'yes', if not, return 'No'	should return "Yes"
2	9	Water temperature - Consistently good water temperature throughout apartment	If all rows that have 9 (a2) in column B on sheet 2 also have a 'Y' in column C on sheet 2 then return 'yes', if not, return 'No'	should return "No"
3	19	Stuff	If all rows that have 19 (a3) in column B on sheet 2 also have a 'Y' in column C on sheet 2 then return 'yes', if not, return 'No'	should return "No"

<tbody>
</tbody>

Sheet2
Row	A	B	C
1	When cold - Is speed /flow rate sufficient? <tbody> </tbody>	8	Y
2	Is hot water hot? <tbody> </tbody>	9	N
3	Is cold water cold? <tbody> </tbody>	9	Y
4	Does the shower head drip? <tbody> </tbody>	19	0
5	Are taps level/ aligned when closed? <tbody> </tbody>	19	0
6	When hot - Is speed /flow rate sufficient? <tbody> </tbody>	8	Y
7	When cold - Is speed /flow rate sufficient? <tbody> </tbody>	8	Y
8	Is hot water hot? <tbody> </tbody>	9	Y
9	Is cold water cold? <tbody> </tbody>	9	Y
10	Does the shower head drip? <tbody> </tbody>	19	0
11	Are taps level/ aligned when closed? <tbody> </tbody>	19	0
12	When warm - Is speed /flow rate sufficient? <tbody> </tbody>	8	Y

<tbody>
</tbody>

I would be very grateful for help!

Thank you!!!

 

[Comfy]

Something like this:

Excel 2010
	A	B
1	8	Yes
2	9	No
3	19	No
Sheet1

Cell Formulas
Range	Formula
B1	=IF(AND(COUNTIF(Sheet2!B:B,Sheet1!A1)=COUNTIFS(Sheet2!B:B,Sheet1!A1,Sheet2!C:C,"Y"),COUNTIF(Sheet2!B:B,Sheet1!A1)>0),"Yes","No")

 

[Ulli8]

Thank you so much, you are a genius, this works a treat!!

 

[Comfy]

You're Welcome.