Coutifs/sumifs and populating with yes or no

[stevembe]

Firstly many thanks for taking the time to read.
In cell K2 I want to count the number of times the phrase “Grade 7” appears in column M8:M200 but at the same time if this phrase appears in column M I need column R8:R200 populated with the word “Yes” and “No” if it doesn’t.
Most grateful for any assistance.

 

[louisH]

Hi,

In K2 :

=COUNTIF($M$8:$M$200, "Grade 7")

In R8 then copy down to R200 :

=IF(M8 = "Grade 7", "Yes", "No")

 

[stevembe]

Hi,

In K2 :

=COUNTIF($M$8:$M$200, "Grade 7")

In R8 then copy down to R200 :

=IF(M8 = "Grade 7", "Yes", "No")

LouisH, thanks so much for the reply and my apologies, I notice I wasn't clear enough. There are Grades 1 to 7 so I am after a formula that works for this e.g, Grade 1, Grade 2, Grade 3 etc

 

[louisH]

The previous formulas assumed "Grade 7" was the only text in the cell. If you want to count cells that CONTAINS "Grade 7" use these :

In K2 :

=COUNTIF($M$8:$M$200, "*Grade 7*")

In R8 then copy down to R200 :

=IF(ISNUMBER(SEARCH("Grade 7",M8)), "Yes","No")

 

[stevembe]

The previous formulas assumed "Grade 7" was the only text in the cell. If you want to count cells that CONTAINS "Grade 7" use these :

In K2 :

=COUNTIF($M$8:$M$200, "*Grade 7*")

In R8 then copy down to R200 :

=IF(ISNUMBER(SEARCH("Grade 7",M8)), "Yes","No")

That's perfect, many thanks for your time, much appreciated