Excel Formula - Identifying Learners

[HollyC]

	A	B	C
1	Learner1	English
2	Learner1	Science
3	Learner1	Geography
4	Learner2	Science
5	Learner2	Geography
6	Learner2	History

<tbody>
</tbody>

I would like a formula that would put a yes by all the subjects a learner is taking if they are studying English as one of there subjects. i.e. learner1 is studying English so I would like a yes is C1,C2 and C3 but learner 2 is not studying English so I would like no in C4, C5 and C6.

I am really struggling with this one so any help would be massively appreciated

 

[Weazel]

maybe...

Excel 2016 (Windows) 32 bit

	A	B	C
1	Learner1	English	yes
2	Learner1	Science	yes
3	Learner1	Geography	yes
4	Learner2	Science	no
5	Learner2	Geography	no
6	Learner2	History	no

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula C1 =IF(COUNTIFS($A$1:$A$6,A1,$B$1:$B$6,"english"),"yes","no") <tbody> </tbody>

<tbody>
</tbody>

 

[HollyC]

Perfect, thank you ever so much and so simple too You my new favourite person

 

[Weazel]

you're welcome