Counting Consecutive Texts

[nicofernandez00]

Hello,

I really need help figuring out the formula for an assignment. On my spreadsheet, I have the monthly status of projects in action. From cells H28-S28, the cells are labeled either "on track", "off-track", "at risk" and "completed". I want to count the amount of consecutive times the projects were labeled "off-track" or "at risk" consecutively.

For instance:

On Track

On Track

On Track

On Track

On Track

At Risk

At Risk

At Risk

At Risk

Off Track

Off track

Off Track

<TBODY>
</TBODY>

The answer here would be 7 because "at risk" and "off track" were the consecutive statuses for seven months straight.

Please help!

 

[nicofernandez00]

Re: Counting Consecutive Text Values

You don't need the extra row

=MAX(FREQUENCY(IF((H28:S28="Off Track")+(H28:S28="At Risk"),COLUMN(H28:S28)),IF(H28:S28="On Track",COLUMN(H28:S28))))

This is an array formula and must be correctly confirmed by entering the formula into the cell, then holding down Shift and Ctrl together and pressing Enter.

This formula is great! Thank you so much. It works for what I need. However, whenever the answer is supposed to be "0", the result is the sum of the "at risk" cells in the row. How can I adjust this so that if the at-risk is not consecutive with the "off-track" it would say "0"?

 

[Domenic]

What if there were more than one occurrence where those values occurred consecutively. For example, what would be the expected result for the following data?

At Risk

At Risk

At Risk

At Risk

Off Track

Off track

Off Track

On Track

At Risk

Off Track

On Track

On Track

<tbody>
</tbody>

 

[jasonb75]

Re: Counting Consecutive Text Values

I'm not able to make the formula do that in testing. Do you have any cells in the range that the formula covers that are empty, blank, or contain something other than "on track", "off track", or "at risk"?

 

[nicofernandez00]

Re: Counting Consecutive Text Values

yes, I also have cells with "not started" "completed" and a few blank cells.

 

[Andrew Poulsom]

How about?

=SUMPRODUCT(--(I28:R28=J28:S28))

 

[nicofernandez00]

Re: Counting Consecutive Text Values

and "on hold"

 

[jasonb75]

Re: Counting Consecutive Text Values

Sorry, looking back, you did say "completed" in your original post, allowing for that and the other possibilities, I think this one works.

=MAX(FREQUENCY(IF((H28:S28="Off Track")+(H28:S28="At Risk"),COLUMN(H28:S28)),IF((H28:S28="Off Track")+(H28:S28="At Risk"),FALSE,COLUMN(H28:S28))))

Needs to be array confirmed as before.

 

[Aladin Akyurek]

Re: Counting Consecutive Text Values

yes, I also have cells with "not started" "completed" and a few blank cells.

Could you create a 2-row sample, one with 0 outcome that you want to see?

 

[nicofernandez00]

Re: Counting Consecutive Text Values

This formula works for the consecutive "at risk"s and "off-track"s but for instance say we have this table:

The answer here should be "0" since there is no "off-track" consecutive with the "at risk" however the formula is giving me "2".

On Track

On Track

At Risk

At Risk

On Track

On Track

On Track

On Track

On Track

On Track

On track

On Track

<TBODY>
</TBODY>

 

[Aladin Akyurek]

Re: Counting Consecutive Text Values

This formula works for the consecutive "at risk"s and "off-track"s but for instance say we have this table:

The answer here should be "0" since there is no "off-track" consecutive with the "at risk" however the formula is giving me "2".

On Track

On Track

At Risk

At Risk

On Track

On Track

On Track

On Track

On Track

On Track

On track

On Track

<TBODY>
</TBODY>

If we replace the two At Risk entries with Off Track entries, what would be the outcome? That is:

On Track

On Track

Off Track

Off Track

On Track

On Track

On Track

On Track

On Track

On Track

On track

On Track

<TBODY>
</TBODY>

And what about?

On Track

On Track

At Risk

At Risk

On Track

Off Track

On Track

On Track

On Track

On Track

On track

On Track

<TBODY>
</TBODY>