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
5
Medium Priority
?
1,144 Views
Last Modified: 2012-05-12
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 .

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



0
Comment
Question by:ibmsddk
  • 3
  • 2
5 Comments
 
LVL 50

Expert Comment

by:barry houdini
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

by:barry houdini
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

=SUMPRODUCT((B2:AD2="Missed")*(C2:AE2="Missed")*(D2:AF2="Missed"))

regards, barry
27391516.xls
0
 

Author Comment

by:ibmsddk
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 .

 Excel snapshot
27391516.xls
0
 
LVL 50

Accepted Solution

by:
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

by:ibmsddk
ID: 36963821
thanks ! Barry it works
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question