There are six classes in the school. Each day pupils record the date, their class (A,B,C,D,E,F), gender (boy / girl) and the fruit they ate that day (freetext, but including apple, pear, and banana) in columns in a worksheet.

I want to find out how many apples, pears and bananas classes A, B and C have been eating.

If I put A, B, C vertically in A1, A2 A3 and Apple, Pear, Banana in B1, B2, B3, this will interpret as Class A and Apple, Class B and Pear, Class C and Banana (ie excluding pears and bananas for A, Apples and Bananas for B, Apples and Pears for C). If I put Apple, Pear, Banana in B4, B5, B6 this will return all the other fruit eaten as well against all the other classes (D,E and F).

How do I set the criteria so that I get all Apple Pear and Banana for only classes A, B and C?

If I want to add the gender (boy/girl) so I can split it even further how do I do that?

Many thanks.

HT