Creating a formula that checks a variable number of columns

New Contributor

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.

"D:\Cafag\Screenshot_1.png" 

"D:\Cafag\Screenshot_2.png" 

 

Any help would be greatly appreciated

Thank you

4 Replies

@Adam_Robinson_ 

Your links aren't valid...

SAMPLE 1

08/09/202215/09/202222/09/2022Att. TotalAtt. Rate
Y  133%
 YY267%
YYY3100%
  Y133%
  Y133%
YYY3100%
Y  133%
  Y133%
  Y133%
 YY267%
 YY267%
Y  133%
  Y133%
YY 267%
YYY3100%
  Y133%
YY 267%
  Y133%
YYY3100%
 Y 133%
  Y133%
 YY267%
  Y133%
  Y133%
Y  133%
YY 267%
  Y133%
  Y133%
YYY3100%
  Y133%
 Y 133%
YYY3100%
 Y 133%
 YY267%
 Y 133%
YY 267%
 Y 133%
 Y 133%
 Y 133%
Y  133%
  Y133%
 YY267%
YYY3100%
 Y 133%
 YY267%
 Y 133%
YY 267%
 YY267%
Y Y267%
 Y 133%
 Y 133%
 Y 133%
YY 267%
  Y133%
 Y 133%
  Y133%
 Y 133%
YYY3100%
  Y133%
YY 267%
YYY3100%
YY 267%
 Y 133%
  Y133%
 Y 133%
 Y 133%
YYY3100%
  Y133%
  Y133%
 YY267%

 

 

SAMPLE 2

08/09/202215/09/202222/09/202229/09/202206/10/2022Att. TotalAtt. Rate
Y    133%
 YY  267%
YYY  3100%
  Y  133%
  Y  133%
YYY  3100%
Y    133%
  Y  133%
  Y  133%
 YY  267%
 YY  267%
Y    133%
  Y  133%
YY   267%
YYY  3100%
  Y  133%
YY   267%
  Y  133%
YYY  3100%
 Y   133%
  Y  133%
 YY  267%
  Y  133%
  Y  133%
Y    133%
YY   267%
  Y  133%
  Y  133%
YYY  3100%
  Y  133%
 Y   133%
YYY  3100%
 Y   133%
 YY  267%
 Y   133%
YY   267%
 Y   133%
 Y   133%
 Y   133%
Y    133%
  Y  133%
 YY  267%
YYY  3100%
 Y   133%
 YY  267%
 Y   133%
YY   267%
 YY  267%
Y Y  267%
 Y   133%
 Y   133%
 Y   133%
YY   267%
  Y  133%
 Y   133%
  Y  133%
 Y   133%
YYY  3100%
  Y  133%
YY   267%
YYY  3100%
YY   267%
 Y   133%
  Y  133%
 Y   133%
 Y   133%
YYY  3100%
  Y  133%
  Y  133%
 YY  267%

 

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?

@Adam_Robinson_ 

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.