Forum Discussion
Creating a formula that checks a variable number of columns
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 attenda...
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?
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.
HansVogelaar Thanks, worked perfectly.