How do I add the last consecutive cells starting from right to left and also reset and/or skip particular cells?

mrjoeybrown

New Member
Joined
Sep 25, 2017
Messages
3
So I have created this awesome spreadsheet (in my opinion) as a school admin but I have at least one thing that has been bugging me for close to 3 months. Here is the data:
last namefirst nameconsecutive daysdays absent8/309/19/49/59/69/7
#1studentYes L4Yes L4Yes L4Yes L4Yes L4
#2studentYes L1Yes L1Yes L1ISSYes L1
#3studentYes L1ABSABS
#4studentYes L3Yes L4Yes L4Yes L4Yes L4
#5studentYes L3OSSYes L3Yes L3Yes L3

<tbody>
</tbody>


I need a counting cell called "CONSECUTIVE DAYS" with a formula to count from right to left the consecutive days a cell has the word "Yes". BUT...If the cell contains an "ISS" or "OSS" then the counting cell needs to reset to "0". If the cell contains an "ABS" or is blank, then it needs to simply skip that cell and keep counting. ALSO, if there is a level change, from say "Yes L4" to a "Yes L3"(reading right to left), then the counting cell needs to reset to "0".'

The previous formula filled in with the appropriate totals for "CONSECUTIVE DAYS":
last namefirst nameconsecutive daysdays absent8/309/19/49/59/69/7
#1student50Yes L4Yes L4Yes L4Yes L4Yes L4
#2student10Yes L1Yes L1Yes L1ISSYes L1
#3student12Yes L1ABSABS
#4student40Yes L3Yes L4Yes L4Yes L4Yes L4
#5student30Yes L3OSSYes L3Yes L3Yes L3


<tbody>
</tbody>
And in final explanation, I do not need to keep up with that number once it has been counted. I just need my teachers to be able to refer to that column on THAT day they are looking at the table.
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Welcome to the MrExcel board.

You're asking for a lot from a formula! :) Still, here's my shot at it:

ABCDEFGHIJKL
1last namefirst nameconsecutive daysdays absent30-Aug1-Sep4-Sep5-Sep6-Sep7-Sep8-Sep9-Sep
2#1student50Yes L4Yes L4Yes L4Yes L4Yes L4
3#2student10Yes L1Yes L1Yes L1ISSYes L1
4#3student12Yes L1ABSABS
5#4student40Yes L3Yes L4Yes L4Yes L4Yes L4
6#5student30Yes L3OSSYes L3Yes L3Yes L3

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet9

Array Formulas
CellFormula
C2{=1/LOOKUP(2,1/FREQUENCY(IF(ISNUMBER(SEARCH(MID(LOOKUP(2,1/ISNUMBER(SEARCH("L",E2:L2)),E2:L2),5,5),E2:L2)),COLUMN(E2:L2)),IF(E2:L2<>"",IF(ISNUMBER(SEARCH("SS",E2:L2)),COLUMN(E2:L2)))))}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>



Even so, I made a few assumptions. First, I'm not looking for "yes", I'm looking for an "L", which appears in every cell that "yes" is in. Next, I'm lumping OSS and ISS together by looking for "SS", since "SS" does not appear in any of your other cells. Finally, the L1 always starts in position 5 in the cell. The formula can be adapted if these assumptions aren't always true.

Finally, if you don't want the formula to be "live", we can write a macro to count the numbers on demand which might be easier.

Let me know what you think.
 
Upvote 0
Well first of all... YOU are the man! or the woman! or heck...I'm so excited...you are the PERSON! You have taken a bit of a weight off of my shoulders. I've been trying things for weeks now.

With that out of the way, there are a few instances where it is not returning the correct count. I've tried to paste a few things from my original file:

=1/LOOKUP(2,1/FREQUENCY(IF(ISNUMBER(SEARCH(MID(LOOKUP(2,1/ISNUMBER(SEARCH("L",Table22[@[8/7]:[12/22]])),Table22[@[8/7]:[12/22]]),5,5),Table22[@[8/7]:[12/22]])),COLUMN(Table22[@[8/7]:[12/22]])),IF(Table22[@[8/7]:[12/22]]<>"",IF(OR(ISNUMBER(SEARCH("SS",Table22[@[8/7]:[12/22]])),ISNUMBER(SEARCH("NO",Table22[@[8/7]:[12/22]]))),COLUMN(Table22[@[8/7]:[12/22]])))))

**This is the formula that I used to produce the 1st row under "CONSECUTIVE DAYS" and then I copied it down. It didn't calculate correctly in A2, A6, A8, and a few others. If it would be helpful for me to share the entire spreadsheet I would be willing. I am new to forums and so my "pasting" is pretty rough. Also, if you notice, I added a "NO" in your search. I had forgotten that this was an option as well.

CONSECUTIVE DAYS Current
Level
1* GREEN
7GREEN
1PURPLE
11PURPLE
1*PURPLE
6GREEN
1*BLUE
10PURPLE
#N/ABLUE
9PURPLE

<colgroup><col style="text-align: center;"><col style="text-align: center;"></colgroup><tbody>
</tbody>


9/79/89/119/129/139/149/159/189/199/209/219/22
Yes L2NONOYes L2Yes L2Yes L2Yes L2Yes L2Yes L3Yes L3Yes L3Yes L3
Yes L2Yes L2Yes L2Yes L2Yes L2Yes L3Yes L3Yes L3Yes L3Yes L3Yes L3Yes L3
NOYes L3Yes L3Yes L3NOYes L4Yes L4Yes L4Yes L4Yes L4NOYes L4
Yes L3Yes L4Yes L4Yes L4Yes L4Yes L4Yes L4Yes L4Yes L4Yes L4Yes L4Yes L4
Yes L4Yes L4
Yes L2Yes L2Yes L2Yes L2Yes L2Yes L2Yes L3Yes L3Yes L3Yes L3Yes L3Yes L3
Yes L1Yes L1Yes L1NONOOSSOSSOSS
Yes L4Yes L4Yes L4Yes L4Yes L4Yes L4Yes L4
Yes L3Yes L3ABSYes L4Yes L4Yes L4Yes L4Yes L4Yes L4Yes L4Yes L4Yes L4

<colgroup><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col span="3" style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col span="3" style="text-align: center;"><col style="text-align: center;"></colgroup><tbody>
</tbody>


Under consecutive days, these are what they are vs. what they should be
1=4
1=2
1=0 (but this one isn't a big deal unless he returns the next day (9/23) with a "YES L1" but it would read "1")
 
Upvote 0
With the addition of "NO" to the mix, I changed it so that I'm explicitly looking for ISS, OSS, or NO to reset the count. Then I needed to use MMULT as the equivalent of an OR. Here's the latest version:

ABCDEFGHIJKLMNOP
1last namefirst nameconsecutive daysdays absent7-Sep8-Sep11-Sep12-Sep13-Sep14-Sep15-Sep18-Sep19-Sep20-Sep21-Sep22-Sep
2#1student40Yes L2NONOYes L2Yes L2Yes L2Yes L2Yes L2Yes L3Yes L3Yes L3Yes L3
3#2student70Yes L2Yes L2Yes L2Yes L2Yes L2Yes L3Yes L3Yes L3Yes L3Yes L3Yes L3Yes L3
4#3student12NOYes L3Yes L3Yes L3NOYes L4Yes L4Yes L4Yes L4Yes L4NOYes L4
5#4student110Yes L3Yes L4Yes L4Yes L4Yes L4Yes L4Yes L4Yes L4Yes L4Yes L4Yes L4Yes L4
6#5student20Yes L4Yes L4
7#66Yes L2Yes L2Yes L2Yes L2Yes L2Yes L2Yes L3Yes L3Yes L3Yes L3Yes L3Yes L3
8#7 3Yes L1Yes L1Yes L1NONOOSSOSSOSS
9#8 7Yes L4Yes L4Yes L4Yes L4Yes L4Yes L4Yes L4
10#90
11#109Yes L3Yes L3ABSYes L4Yes L4Yes L4Yes L4Yes L4Yes L4Yes L4Yes L4Yes L4
12#110
13

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet11

Array Formulas
CellFormula
C2{=IFERROR(1/LOOKUP(2,1/FREQUENCY(IF(ISNUMBER(SEARCH(MID(LOOKUP(2,1/ISNUMBER(SEARCH("L",E2:Z2)),E2:Z2),5,5),E2:Z2)),COLUMN(E2:Z2)),IF(E2:Z2<>"",IF(MMULT({1,1,1},ISNUMBER(SEARCH({"OSS";"ISS";"NO"},E2:Z2))+0),COLUMN(E2:Z2))))),0)}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>



With this version, I get all the expected results you mention, EXCEPT:

On #7 , I don't see why you expect 0. You have 3 OSS's, 2 NO's, then 3 L1s.
On #8 , you have 10. I assume you must have data for that row that doesn't show, since there are only 7 L4's on that row that I see.

I'll leave it to you to convert it to table format, since you seem to have that sussed out. Let me know if this works for you.
 
Upvote 0
I swear my brain is mush sometimes. If we're looking for the whole cell value, we don't need to use SEARCH, and we can take out the check for an empty cell too:

=IFERROR(1/LOOKUP(2,1/FREQUENCY(IF(ISNUMBER(SEARCH(MID(LOOKUP(2,1/ISNUMBER(SEARCH("L",E2:Z2)),E2:Z2),5,5),E2:Z2)),COLUMN(E2:Z2)),IF(MMULT({1,1,1},IF(E2:Z2={"OSS";"ISS";"NO"},1,0)),COLUMN(E2:Z2)))),0)

with CSE.
 
Last edited:
Upvote 0
You are amazing and brilliant. I believe that my data will be much more reliable now. I thank you (although our students might not appreciate the efficiency).
 
Upvote 0
Glad this works for you! :cool:

And just tell your students that it's good for them!
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,253
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top