Sep 30 2022 04:05 AM
Hi, have what I assume to be a relatively simple query.
I'm trying to create a formula for a spreadsheet that I have which I need to add columns periodically to (one for each week) to log attendance.
I want to write a formula which I don't have to edit when I add new columns in that counts the number of attendances. For instance if I have two date columns, and I have a third, the formula that I'm using will still only check the first two columns, and I have to change it to check all three, and I don't want to have to do that.
So basically, a formula that checks from Column A to the Column before the one with data 'Total' in.
Here's some screenshots to illustrate the problem better.
Any help would be greatly appreciated
Thank you
Sep 30 2022 05:57 AM
Your links aren't valid...
Sep 30 2022 07:00 AM
SAMPLE 1
08/09/2022 | 15/09/2022 | 22/09/2022 | Att. Total | Att. Rate |
Y | 1 | 33% | ||
Y | Y | 2 | 67% | |
Y | Y | Y | 3 | 100% |
Y | 1 | 33% | ||
Y | 1 | 33% | ||
Y | Y | Y | 3 | 100% |
Y | 1 | 33% | ||
Y | 1 | 33% | ||
Y | 1 | 33% | ||
Y | Y | 2 | 67% | |
Y | Y | 2 | 67% | |
Y | 1 | 33% | ||
Y | 1 | 33% | ||
Y | Y | 2 | 67% | |
Y | Y | Y | 3 | 100% |
Y | 1 | 33% | ||
Y | Y | 2 | 67% | |
Y | 1 | 33% | ||
Y | Y | Y | 3 | 100% |
Y | 1 | 33% | ||
Y | 1 | 33% | ||
Y | Y | 2 | 67% | |
Y | 1 | 33% | ||
Y | 1 | 33% | ||
Y | 1 | 33% | ||
Y | Y | 2 | 67% | |
Y | 1 | 33% | ||
Y | 1 | 33% | ||
Y | Y | Y | 3 | 100% |
Y | 1 | 33% | ||
Y | 1 | 33% | ||
Y | Y | Y | 3 | 100% |
Y | 1 | 33% | ||
Y | Y | 2 | 67% | |
Y | 1 | 33% | ||
Y | Y | 2 | 67% | |
Y | 1 | 33% | ||
Y | 1 | 33% | ||
Y | 1 | 33% | ||
Y | 1 | 33% | ||
Y | 1 | 33% | ||
Y | Y | 2 | 67% | |
Y | Y | Y | 3 | 100% |
Y | 1 | 33% | ||
Y | Y | 2 | 67% | |
Y | 1 | 33% | ||
Y | Y | 2 | 67% | |
Y | Y | 2 | 67% | |
Y | Y | 2 | 67% | |
Y | 1 | 33% | ||
Y | 1 | 33% | ||
Y | 1 | 33% | ||
Y | Y | 2 | 67% | |
Y | 1 | 33% | ||
Y | 1 | 33% | ||
Y | 1 | 33% | ||
Y | 1 | 33% | ||
Y | Y | Y | 3 | 100% |
Y | 1 | 33% | ||
Y | Y | 2 | 67% | |
Y | Y | Y | 3 | 100% |
Y | Y | 2 | 67% | |
Y | 1 | 33% | ||
Y | 1 | 33% | ||
Y | 1 | 33% | ||
Y | 1 | 33% | ||
Y | Y | Y | 3 | 100% |
Y | 1 | 33% | ||
Y | 1 | 33% | ||
Y | Y | 2 | 67% |
SAMPLE 2
08/09/2022 | 15/09/2022 | 22/09/2022 | 29/09/2022 | 06/10/2022 | Att. Total | Att. Rate |
Y | 1 | 33% | ||||
Y | Y | 2 | 67% | |||
Y | Y | Y | 3 | 100% | ||
Y | 1 | 33% | ||||
Y | 1 | 33% | ||||
Y | Y | Y | 3 | 100% | ||
Y | 1 | 33% | ||||
Y | 1 | 33% | ||||
Y | 1 | 33% | ||||
Y | Y | 2 | 67% | |||
Y | Y | 2 | 67% | |||
Y | 1 | 33% | ||||
Y | 1 | 33% | ||||
Y | Y | 2 | 67% | |||
Y | Y | Y | 3 | 100% | ||
Y | 1 | 33% | ||||
Y | Y | 2 | 67% | |||
Y | 1 | 33% | ||||
Y | Y | Y | 3 | 100% | ||
Y | 1 | 33% | ||||
Y | 1 | 33% | ||||
Y | Y | 2 | 67% | |||
Y | 1 | 33% | ||||
Y | 1 | 33% | ||||
Y | 1 | 33% | ||||
Y | Y | 2 | 67% | |||
Y | 1 | 33% | ||||
Y | 1 | 33% | ||||
Y | Y | Y | 3 | 100% | ||
Y | 1 | 33% | ||||
Y | 1 | 33% | ||||
Y | Y | Y | 3 | 100% | ||
Y | 1 | 33% | ||||
Y | Y | 2 | 67% | |||
Y | 1 | 33% | ||||
Y | Y | 2 | 67% | |||
Y | 1 | 33% | ||||
Y | 1 | 33% | ||||
Y | 1 | 33% | ||||
Y | 1 | 33% | ||||
Y | 1 | 33% | ||||
Y | Y | 2 | 67% | |||
Y | Y | Y | 3 | 100% | ||
Y | 1 | 33% | ||||
Y | Y | 2 | 67% | |||
Y | 1 | 33% | ||||
Y | Y | 2 | 67% | |||
Y | Y | 2 | 67% | |||
Y | Y | 2 | 67% | |||
Y | 1 | 33% | ||||
Y | 1 | 33% | ||||
Y | 1 | 33% | ||||
Y | Y | 2 | 67% | |||
Y | 1 | 33% | ||||
Y | 1 | 33% | ||||
Y | 1 | 33% | ||||
Y | 1 | 33% | ||||
Y | Y | Y | 3 | 100% | ||
Y | 1 | 33% | ||||
Y | Y | 2 | 67% | |||
Y | Y | Y | 3 | 100% | ||
Y | Y | 2 | 67% | |||
Y | 1 | 33% | ||||
Y | 1 | 33% | ||||
Y | 1 | 33% | ||||
Y | 1 | 33% | ||||
Y | Y | Y | 3 | 100% | ||
Y | 1 | 33% | ||||
Y | 1 | 33% | ||||
Y | Y | 2 | 67% |
If I use the formula '=COUNTIF(A2:C2)' for Att. Total, I have to change it to '=COUNTIF(A2:E2)' manually, is there an alternative formula I can use which I don't need to do this for?
Sep 30 2022 07:39 AM
For Att. Total:
=COUNTIF($A2:INDEX(2:2,MATCH("Att. Total",$1:$1)-1),"Y")
For Att. Rate:
=D2/(MATCH("Att. Total",$1:$1)-1)
Excel will automatically adjust D2 when you insert more columns.
Oct 02 2022 04:39 AM
@Hans Vogelaar Thanks, worked perfectly.