Formula to count the number of events of consecutive values

**malaka76** · 01-06-2020, 06:57 AM

Hi,
I need to create a formula that looks at my roster and counts the number of instances of "S" appearing in the roster where consecutive "S" values are counted as 1 event.

I have attached a sample spreadsheet. The cells in yellow are where I wish the formula to reside.

Secondly is there a way to ignore blank cells between the S to count the event as 1?

Thanks in advance for all you help

**WideBoyDixon** · 01-06-2020, 08:08 AM

Formula for AH3:

Please Login or Register  to view this content.

Enter with Ctrl+Shift+Enter to create an array formula. However, that does give a result of 2 for the first employee based on the two separate runs of "S" (B3:F3 and J3:J3).

WBD

**WideBoyDixon** · 01-06-2020, 08:11 AM

Perhaps:

Please Login or Register  to view this content.

It seems to produce the right result!!

WBD

Formula to count the number of events of consecutive values

LinkBack

Thread Tools

Rate This Thread

Display

Formula to count the number of events of consecutive values

Re: Formula to count the number of events of consecutive values

Re: Formula to count the number of events of consecutive values

Thread Information

Users Browsing this Thread

Similar Threads

count the number of consecutive values >=2

Count consecutive duplicate values and then divide by that number

Formula to count number of consecutive values in previous cells

Need Formula for calculating average of specified number of consecutive non-blank values

Formula to Count Events Between Specific Dates and Having Adjacent Values >0

[SOLVED] I need COUNTIFS or SUMPRODUCT formula to count ONLY Non-Consecutive values

Over Different Rows - count the number of consecutive values >=2

Bookmarks

Bookmarks

Posting Permissions