Thread: [Solved] need help in counting the number of rows that contain 5 consecutive X
View Single Post
 
Old 11-20-2018, 12:03 AM
xor xor is offline Windows 10 Office 2016
Expert
  
Join Date: Oct 2015
Posts: 1,100
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default
In the formula in your first post you used:

=SUM(IF(FREQUENCY(IF(ISNUMBER(SEARCH("X",$A1:$G1)) ,COLUMN($A1:$G1)-COLUMN($A1)+1),IFERROR(SEARCH("X",$A1:$G1),COLUMN( $A1:$G1)-COLUMN($A1)+1))>=5,1)) and in the formula in the most recent file:

SUM(IF(FREQUENCY(IF(ISNUMBER(SEARCH("X",$A1:$G1)), COLUMN($A1:$G1)-COLUMN($A1)+1),IF(ISERROR(SEARCH("X",$A1:$G1)),COLUMN($A1:$G1)-COLUMN($A1)+1))>=5,1))

Another possibility is the somewhat shorter formula:

=--(MAX(FREQUENCY(IF(ISNUMBER(SEARCH("X",$A1:$G1)),CO LUMN($A1:$G1)),IF(NOT(ISNUMBER(SEARCH("X",$A1:$G1) )),COLUMN($A1:$G1))))>=5)
Reply With Quote