Counting certain text in a selection

JackDanIce

Well-known Member

Joined

Feb 3, 2010

Messages

9,922

Office Version

1. 365

Platform

1. Windows

• Aug 3, 2015

• #2

Try:

Code:

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

 

Upvote 0

M

Mackers

Well-known Member

Joined

Nov 4, 2013

Messages

536

• Aug 3, 2015

• #3

Hi

If you look at your OR() function you will probably see where you've gone wrong:
=(IF(OR(COUNTIF(K7:M28,"No - Major")>0,1,COUNTIF(K7:M28,"No - Minor")>2,2),""))

What I would do is use a nested IF() like this - note that this way round means the 1 takes priority over the 2:

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

Hope that helps

Mackers

Edit: Jack got there first, darn

 

Upvote 0

2

28creation

Board Regular

Joined

Oct 13, 2014

Messages

124

• Aug 3, 2015

• #4

Thanks Mackers that works great.

If realised I need something a little different, my error, apologies.

I need one or two "No - Minor"s to result in a 2, but three or more to result in a 1.

Thanks.

 

Upvote 0

JackDanIce

Well-known Member

Joined

Feb 3, 2010

Messages

9,922

Office Version

1. 365

Platform

1. Windows

• Aug 3, 2015

• #5

Try:

Code:

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

 

Upvote 0

2

28creation

Board Regular

Joined

Oct 13, 2014

Messages

124

• Aug 3, 2015

• #6

JackDanIce said:

Try:

Code:

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

Click to expand...

Contains an error apparently.

 

Upvote 0

JackDanIce

Well-known Member

Joined

Feb 3, 2010

Messages

9,922

Office Version

1. 365

Platform

1. Windows

• Aug 3, 2015

• #7

Typo I think, try:

Rich (BB code):

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

 

Upvote 0

2

28creation

Board Regular

Joined

Oct 13, 2014

Messages

124

• Aug 3, 2015

• #8

JackDanIce said:

Typo I think, try:

Rich (BB code):

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

Click to expand...

This has too many arguments.

 

Upvote 0

JackDanIce

Well-known Member

Joined

Feb 3, 2010

Messages

9,922

Office Version

1. 365

Platform

1. Windows

• Aug 3, 2015

• #9

Rich (BB code):

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

 

Upvote 0

2

28creation

Board Regular

Joined

Oct 13, 2014

Messages

124

• Aug 3, 2015

• #10

Had to take the comma out after the 3. Works great thanks.

Sorry, can another thing be added please?

If this criteria is successfully avoided the result should be a 3. So no "No - Minor" or "No - Major" at all (forgot to mention this earlier, apologies).

 

Upvote 0