Count IF Conundrum in conditional Format!

[VBA learner ITG]

Good morning,

I am trying to apply a conditional format to highlight where a number sequence in column Q appears more than 4 times OR 5 times in that column.

I have tried this formula but my logic is out and I need your guidance.

below is the formula logic i am working on:

=countif(Q:Q, Q3) >4

[TABLE="width: 500"]
<tbody>[TR]
[TD]COLUMN Q [/TD]
[/TR]
[TR]
[TD]
[/TD]
[/TR]
[TR]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

 

[snjpverma]

Your formula is correct. it should work properly.
Let us know what problem you are facing with this formula

 

[VBA Geek]

Excel 2010

	A	B
CONSECUTIVES

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Sequences[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]{=IF(A2<>A1,MATCH(TRUE,$A2:$A$14<>$A3:$A$15,0),B1)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



and drag down. with this you can build your Condition format

 

[VBA learner ITG]

Hi Snjpverma,

The attached imagine is how the conditional format is highlighting the column cell range data.

https://wetransfer.com/downloads/3b...67b89a916c1d0cbea258521d20180119101626/e6b7e8

 

[VBA learner ITG]

I guess i need to build this formula sequence in column R before i can build the conditional format?

I am trying not have to build another formula if possible as the i thought a countIF formula would do the trick.

Excel 2010

	A	B
CONSECUTIVES

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Sequences[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]B2[/TH]
[TD="align: left"]{=IF(A2<>A1,MATCH(TRUE,$A2:$A$14<>$A3:$A$15,0),B1)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



and drag down. with this you can build your Condition format

 

[anglais428]

Try changing Q3 to Q1
=countif(Q:Q, Q1) >4

 

[VBA Geek]

I guess i need to build this formula sequence in column R before i can build the conditional format?

I am trying not have to build another formula if possible as the i thought a countIF formula would do the trick.

No, you do not need another column. In my example, you'd select A2, go to condition formatting and add this as a rule

=IF(A2<>A1,MATCH(TRUE,$A2:$A$14<>$A3:$A$15,0),B1)>=4

then you'd use FORMAT PAINTER to apply the same CF to all the other cells in the row