Formula to determine attendance within specific timeframe

[InevitableFile7566]

I am working on an attendance tracker where I need to calculate for individuals participating in workshops, but during their current term appointment. In order to calculate an attendance rate, I need some help with a COUNTIFS formula (or at least I am pretty sure it would be COUNTIFS) that would count the number of occurrences of "Present" during the latest term dates (or during workshops that occurred on a date equal to or greater than the start date of the highest term — Term 3 for Person A and Term 2 for Person B). Currently what I have in cells K3 and K4 is below, but I can't quite figure out why the formula is returning 0 instead of 4 (Person A) and 2 (Person B).



=COUNTIFS(Table1[@[LEAVE BLANK]:[2022-09-01]],"Present",Table1[[#Headers],[LEAVE BLANK]:[2022-09-01]],">="&LARGE(Table1[@[TERM 1 START]:[TERM 4 END]],2))

 

[StephenCrump]

Welcome to the Forum!

Excel table column headers are always text - you can't make them numeric. So the problem is that you're comparing dates (numeric) to column headers (text) and not getting matches, even though they look the same.

One way to work around this would be:

=SUM((Table1[@[Leave Blank]:[2022-09-01]]="Present")*IFERROR((--Table1[[#Headers],[Leave Blank]:[2022-09-01]])>=LARGE(Table1[@[Term 1 start]:[Term 4 end]],2),0))

 

[InevitableFile7566]

Welcome to the Forum!

Excel table column headers are always text - you can't make them numeric. So the problem is that you're comparing dates (numeric) to column headers (text) and not getting matches, even though they look the same.

One way to work around this would be:

=SUM((Table1[@[Leave Blank]:[2022-09-01]]="Present")*IFERROR((--Table1[[#Headers],[Leave Blank]:[2022-09-01]])>=LARGE(Table1[@[Term 1 start]:[Term 4 end]],2),0))

Wow, thank you so much, this worked perfectly and is incredibly helpful! I had no idea about the headers always being read as text, so thanks for sharing that info. Many thanks again!