Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# Excel Formula to count consecutive occurence of word in Row

Posted on 2011-10-11
Medium Priority
1,144 Views
I need to count the concequtive occurence of word " Missed " and " Failed " in a Row .

Refer to image attached .

in that  in Row 2  ,3 times  consecutively missed  is there .

and in row 3   only 1 occurrence of consecutive 3 missed status  is there .

pls provide a formula for calculating the same for 31 days . ( 31 columns will there )

0
Question by:ibmsddk
• 3
• 2

LVL 50

Expert Comment

ID: 36951468
If there are instances of 4 "Missed" in a row then would that count as 1 (or does it count as 2 instances), e.g. 3 in A2:C2 and 3 in B2:D2?

regards, barry
0

LVL 50

Expert Comment

ID: 36951534
Assuming all instances of 3 or more are counted just once you can use this "array formula"

=SUM(IF(FREQUENCY(IF(B2:AF2="Missed",COLUMN(B2:AF2)),IF(B2:AF2<>"Missed",COLUMN(B2:AF2)))>=3,1))

confirmed with CTRL+SHIFT+ENTER

See attached example, formulas in column A - press F9 to re-generate random data

PS this version would count every instance of 3 consecutive - even those contained within longer runs

regards, barry
27391516.xls
0

Author Comment

ID: 36954894
Please refer the excel sheet and formula calculating consecutive 3 is wrong ,answer  should be 5 , but formula  computing as 4  ,also consecutive occurrence of 7 event is also wrong its showing as 2 but  correct  value is 3  i have highlighted it in borders , pls help in correcting the same

also refer the formulas in excel attached .

27391516.xls
0

LVL 50

Accepted Solution

barry houdini earned 1500 total points
ID: 36956545
My suggested formula was counting any run or 3 or more as 1 instance as I said, you want to count a run of 6 as two instances, 9 as 3 instances etc so change to this version

=SUM(INT(FREQUENCY(IF(C2:AG2="Missed",COLUMN(C2:AG2)),IF(C2:AG2<>"Missed",COLUMN(C2:AG2)))/3))

confirmed with CTRL+SHIFT+ENTER

see attached revised version

Obviously for 7 consecutive you can use the same formula with the 3 at the end changed to a 7

regards, barry
27391516v2.xls
0

Author Closing Comment

ID: 36963821
thanks ! Barry it works
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.