Trying to calculate the number of entries between 2 columns on an attendance table

[hunterdraygon]

Hi all,

I have a workbook for attendances to a martial arts club that I wish to calculate a few different aspects in order to make the day to day input simpler for the club admin (voluntary role so I don't want her to spend too long doing diary entries/weekly checks etc)

My problem:
I am trying to calculate the number of attendances a student has between grading's. i.e. if a student last graded 01/01/17 and there are 2 sessions per week (not all sessions would be attended), and as of todays date (06/03/17), how many sessions they have been attended.

My way of thinking was if the grading day was session 3 and todays day was session 15, a formula would search between session 3 and 15 to count the number of sessions attended. I'm hoping someone would be able to assist me with calculating a formula (or a way) to ensure I can do this automatically. (any other recommendations for the overall workbook working would be appreciated too).

I have attached the whole workbook, its not pretty because I wanted the functionality working first. The Attendance worksheet is the main table with a sample attendance record, The Gradings worksheet is where the sessions attended is being calculated.

Please and thank you in advance
Kind Regards
Alan

[philaugust2004]

what signifies attendance at a session?

[hunterdraygon]

Hi philaugust2004, thank you for your response.

An attendance is defined by an E (no need to pay), P (paid) or U (unpaid), I can remove the C option, making it any data within the table if that makes it easier.
The sample data is the attendance since thurs 23/02 (session 16) to today Mon 06/03 (session 19).

Kind regards
Alan

[philaugust2004]

I have a formula but it's HUGE and I am sure it is way over the top and someone far more talented than me can find a solution with a short formula

[FDibbins]

I have a formula but it's HUGE and I am sure it is way over the top and someone far more talented than me can find a solution with a short formula

Lets see it? maybe thats what it needs to be, or it may get someone else thinking for something better?

[FDibbins]

Could you give a few sample answers, just so we know what you want?

Im thinking that to start, a formula like this will give you the number for the "last renewal" date...
Budo F9=IFERROR(INDEX(Attendance!$C$9:$AA$9,,MATCH('Budo Passes'!C9,Attendance!$C$8:$AA$8)),"")
copied down

[philaugust2004]

=IFERROR(COUNTIF(INDIRECT("Attendance!"&VLOOKUP(MATCH(E10,Attendance!$A$8:$AA$8,0),$N$7:$O$35,2,0)&MATCH(B10,Attendance!$B$1:$B$38,0)&":"&CELL("address",AA10)), "E")+COUNTIF(INDIRECT("Attendance!$"&VLOOKUP(MATCH(E10,Attendance!$A$8:$AA$8,0),$N$7:$O$35,2,0)&MATCH(B10,Attendance!$B$1:$B$38,0)&":"&CELL("address",AA10)), "P")+COUNTIF(INDIRECT("Attendance!$"&VLOOKUP(MATCH(E10,Attendance!$A$8:$AA$8,0),$N$7:$O$35,2,0)&MATCH(B10,Attendance!$B$1:$B$38,0)&":"&CELL("address",AA10)), "U"),"")

put in I10 in gradings and copy down
Pretty sure someone can do one many times shorter!

[FDibbins]

yes, that is quite a mouthful, but it may inspire others to work on it

[hunterdraygon]

An example would be. Gary f, Last graded in session 1, today is session 15 with attendance on 3 occasions. Therefore count all filled in columns for gary f between sessions 1 and 15.
i would then be able to workout how many sessions still remain before he is eligble to grade again.

Could you give a few sample answers, just so we know what you want?

Im thinking that to start, a formula like this will give you the number for the "last renewal" date...
Budo F9=IFERROR(INDEX(Attendance!$C$9:$AA$9,,MATCH('Budo Passes'!C9,Attendance!$C$8:$AA$8)),"")
copied down

[hunterdraygon]

damn, i was expecting something with a few different functions but i wasnt expecting that many. Would it be any easier if i defined names, tables or something similar?

=IFERROR(COUNTIF(INDIRECT("Attendance!"&VLOOKUP(MATCH(E10,Attendance!$A$8:$AA$8,0),$N$7:$O$35,2,0)&MATCH(B10,Attendance!$B$1:$B$38,0)&":"&CELL("address",AA10)), "E")+COUNTIF(INDIRECT("Attendance!$"&VLOOKUP(MATCH(E10,Attendance!$A$8:$AA$8,0),$N$7:$O$35,2,0)&MATCH(B10,Attendance!$B$1:$B$38,0)&":"&CELL("address",AA10)), "P")+COUNTIF(INDIRECT("Attendance!$"&VLOOKUP(MATCH(E10,Attendance!$A$8:$AA$8,0),$N$7:$O$35,2,0)&MATCH(B10,Attendance!$B$1:$B$38,0)&":"&CELL("address",AA10)), "U"),"")

put in I10 in gradings and copy down
Pretty sure someone can do one many times shorter!

[FDibbins]

An example would be. Gary f, Last graded in session 1, today is session 15

Based on the data provided, Im not sure how you arrive at those dates?
Budo C9 says gary F graded on 2/28, which is session 17, and for today's date (3/6), that session is not even shown?

[Pete_UK]

... if the grading day was session 3 and todays day was session 15 ...

How would the system know that the last grading day was session 3?

Pete

[Pete_UK]

I think you could use this in cell I10 of the Gradings sheet (if that's where you want it to go - it isn't clear from your description):

=SUM(COUNTIFS(Attendance!$C10:$AA10,{"P","U","E"},Attendance!$C$8:$AA$8,">"&E10,Attendance!$C$8:$AA$8,"<="&TODAY()))

then copy down.

Hope this helps.

Pete

[philaugust2004]

ok

I have reduced my stupidly long formula to this
=IFERROR(SUM(COUNTIFS(INDIRECT("Attendance!"&VLOOKUP(MATCH(E10,Attendance!$A$8:$AA$8,0),$N$7:$O$35,2,0)&MATCH(B10,Attendance!$B$1:$B$38,0)&":"&CELL("address",AA10)),{"P","U","E"})),"")

You will still need a lookup table at n7:035bin grading sheet but this is just the numbrrs 1-27 in N and column letters in O (A,B,C etc to AA)

Formula looks at date of last grading from grading sheet and then counts sessions since that date where the student has attended.