Counting certain text in a selection

[28creation]

Hi all,


I've got this code, & it's not bringing back the results I'm after.

=(IF(OR(COUNTIF(K7:M28,"No - Major")>0,1,COUNTIF(K7:M28,"No - Minor")>2,2),""))

K7-M28 are the cells (columns K, L & M are merged cells on each row).

Basically if any of these cells have a "No - Major" in them then the result in K29 should be 1, but if any have "No - Minor" three times or more than this should result in a 2.


Hope you can help!

Thanks.

 

[JackDanIce]

We're getting there! Try:

=IF(COUNTIF(K7:M28,"No - Major"),1,IFERROR(LOOKUP(COUNTIF(K7:M28,"No - Minor"),{1,2,3},{2,2,1}),3))

 

[28creation]

That's great. Nearly there!

I need the result cell to stay blank if there's nothing in the selected cells.

Thanks!

 

[JackDanIce]

Try:

=IF(COUNTA(K7:M28)=0,"",IF(COUNTIF(K7:M28,"No - Major"),1,IFERROR(LOOKUP(COUNTIF(K7:M28,"No - Minor"),{1,2,3},{2,2,1}),3)))

 

[28creation]

Try:

=IF(COUNTA(K7:M28)=0,"",IF(COUNTIF(K7:M28,"No - Major"),1,IFERROR(LOOKUP(COUNTIF(K7:M28,"No - Minor"),{1,2,3},{2,2,1}),3)))

Brilliant, thank you so much!!!

 

[JackDanIce]

You're welcome.