Check which value appeared three times in a range

[adiritz]

I have following formula in Cell B11.

=IF((COUNTA(A2:A10)-COUNTIF(A2:A10,A2))=6,"Y","")

It checks if the value of A2 cell appeared 3 times in a last nine rows and show Y or else remain blank.

I want to check values of all 9 cells from A2:A10 and display VALUE in B11 only if following conditions are met:

1. there is only one value in 9 cells which have appeared 3 times. All other values need to be 2 or below.


So for eg.

A
A
B
C
D
E
F
A

IT SHOULD DISPLAY 'A'

A
B
B
B
B
C
D
D
D

It should display NONE, as more than one value have appeared 3 times or more

A
A
A
B
A
C
D
E
F

It should display NONE, as A has appeared more than 3 times in last 9 rows

Thanks for all your help.

 

[Marcelo Branco]

The first set has only 8 values, is this correct?

M.

 

[adiritz]

The first set has only 8 values, is this correct?

M.

Apologies, it should have 9.

Thanks

 

[Aladin Akyurek]

What is the result when the following is given?

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][/tr][tr][td]

2​

[/td][td]D[/td][/tr]
[tr][td]

3​

[/td][td]B[/td][/tr]
[tr][td]

4​

[/td][td]B[/td][/tr]
[tr][td]

5​

[/td][td]B[/td][/tr]
[tr][td]

6​

[/td][td]B[/td][/tr]
[tr][td]

7​

[/td][td]C[/td][/tr]
[tr][td]

8​

[/td][td]D[/td][/tr]
[tr][td]

9​

[/td][td]D[/td][/tr]
[tr][td]

10​

[/td][td]D[/td][/tr]
[tr][td]

11​

[/td][td]B[/td][/tr]
[/table]

What is the result if D of A2 occurs 4 times?

 

[Marcelo Branco]

Maybe something like this


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Values​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td="bgcolor:#D9D9D9"]

A​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td="bgcolor:#D9D9D9"]

A​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td="bgcolor:#D9D9D9"]

B​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td="bgcolor:#D9D9D9"]

C​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td="bgcolor:#D9D9D9"]

D​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td="bgcolor:#D9D9D9"]

E​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td="bgcolor:#D9D9D9"]

F​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td="bgcolor:#D9D9D9"]

A​

[/td][td]

Value​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td="bgcolor:#D9D9D9"]

F​

[/td][td]

A​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td="bgcolor:#F2DCDB"]

A​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

12​

[/td][td="bgcolor:#F2DCDB"]

B​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

13​

[/td][td="bgcolor:#F2DCDB"]

B​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

14​

[/td][td="bgcolor:#F2DCDB"]

B​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

15​

[/td][td="bgcolor:#F2DCDB"]

B​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

16​

[/td][td="bgcolor:#F2DCDB"]

C​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

17​

[/td][td="bgcolor:#F2DCDB"]

D​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

18​

[/td][td="bgcolor:#F2DCDB"]

D​

[/td][td]

Value​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

19​

[/td][td="bgcolor:#F2DCDB"]

D​

[/td][td]

NONE​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

20​

[/td][td="bgcolor:#D9D9D9"]

A​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

21​

[/td][td="bgcolor:#D9D9D9"]

A​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

22​

[/td][td="bgcolor:#D9D9D9"]

A​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

23​

[/td][td="bgcolor:#D9D9D9"]

B​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

24​

[/td][td="bgcolor:#D9D9D9"]

A​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

25​

[/td][td="bgcolor:#D9D9D9"]

C​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

26​

[/td][td="bgcolor:#D9D9D9"]

D​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

27​

[/td][td="bgcolor:#D9D9D9"]

E​

[/td][td]

Value​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

28​

[/td][td="bgcolor:#D9D9D9"]

F​

[/td][td]

NONE​

[/td][/tr]
[/table]

Array formula in B10
=IF(AND(SUMPRODUCT(--(COUNTIF(A2:A10,A2:A10)=3))=3,SUMPRODUCT(--(COUNTIF(A2:A10,A2:A10)>3))=0),INDEX(A2:A10,MATCH(3,COUNTIF(A2:A10,A2:A10),0)),"NONE")
Ctrl+Shift+Enter

Select B9:B10 and copy (Ctrl+C)
Select B18:B19 and paste (Ctrl+V)
Select B27:B28 and paste (Ctrl+V)

Hope this helps

M.

 

[adiritz]

It worked flawlessly.

Thanks a lot, this will be a great help for me.

 

[Marcelo Branco]

You are welcome.

M.