Counting Consecutive Texts

bs0d

Well-known Member

Joined

Dec 29, 2006

Messages

622

• Jun 24, 2014

• #2

perhaps:

Code:

=COUNTIF(H28:S28, "At Risk")+COUNTIF(H28:S28, "Off Track")

 

Upvote 0

N

nicofernandez00

New Member

Joined

Jun 24, 2014

Messages

27

• Jun 24, 2014

• #3

bs0d said:

perhaps:

Code:

=COUNTIF(H28:S28, "At Risk")+COUNTIF(H28:S28, "Off Track")

Click to expand...

It worked! Thank you so much! However, on one row of cells, there is an extra "At Risk" that is not consecutive with the set and the formula still counts it. For example:

The clear answer here would be 7, however, the formula gives me 8 due to the "at risk" in the second cell.

[On Track]

[At Risk]

[On Track]

[On Track]

[On Track]

[At Risk]

[At Risk]

[At Risk]

[At Risk]

[Off Track]

[Off track]

[Off Track]

<TBODY>
</TBODY>

 

Last edited: Jun 24, 2014

Upvote 0

bs0d

Well-known Member

Joined

Dec 29, 2006

Messages

622

• Jun 24, 2014

• #4

Make sure the range in the formula is referencing what you intend. The reference may change without using an absolute reference to lock it in. This would involve adding a $ before the column (to lock the column), and row (to lock the row).

$H$28:$S$28 (locks column and row)
H$28:S$28 (locks row only)
$H28:$S28 (locks column only)

 

Upvote 0

N

nicofernandez00

New Member

Joined

Jun 24, 2014

Messages

27

• Jun 24, 2014

• #5

bs0d said:

Make sure the range in the formula is referencing what you intend. The reference may change without using an absolute reference to lock it in. This would involve adding a $ before the column (to lock the column), and row (to lock the row).

$H$28:$S$28 (locks column and row)
H$28:S$28 (locks row only)
$H28:$S28 (locks column only)

Click to expand...

It turns out that I was plugging the formula into the wrong cells. The formula did not work for me because it simply adds up the "At risk" and "off track". It does not count them as consecutive.

 

Upvote 0

N

nicofernandez00

New Member

Joined

Jun 24, 2014

Messages

27

• Jun 24, 2014

• #6

Anyone?

 

Upvote 0

N

nicofernandez00

New Member

Joined

Jun 24, 2014

Messages

27

• Jun 24, 2014

• #7

Counting Consecutive Text Values

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

At Risk

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. The third "At Risk" listed should not be included.

Please help!​

 

Upvote 0

R

Redwolfx

Well-known Member

Joined

Feb 22, 2013

Messages

1,161

• Jun 24, 2014

• #8

Re: Counting Consecutive Text Values

If you add a cheater Row

=IF(AND(OR(H28="Off Track",H28="At Risk"),OR(OR(G28="Off Track",G28="At Risk"),OR(I28="Off Track",I28="At Risk"))),1,0) Drag Right

Then

=SUM(H29:S29)

 

Upvote 0

N

nicofernandez00

New Member

Joined

Jun 24, 2014

Messages

27

• Jun 24, 2014

• #9

Re: Counting Consecutive Text Values

Redwolfx said:

If you add a cheater Row

=IF(AND(OR(H28="Off Track",H28="At Risk"),OR(OR(G28="Off Track",G28="At Risk"),OR(I28="Off Track",I28="At Risk"))),1,0) Drag Right

Then

=SUM(H29:S29)

Click to expand...

How exactly do you do this? I'm sorry I am not quite excel savvy.

 

Upvote 0

J

jasonb75

Well-known Member

Joined

Dec 30, 2008

Messages

15,507

Office Version

1. 365

Platform

1. Windows

• Jun 24, 2014

• #10

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.

 

Upvote 0