Conditional Counting Formula (Date)

[RubenJ]

I have an Excel-sheet on which to administer absence from a sports team. The cells in the sheet are filled with textual input (reason of absence and date of notification).

The first row exists of dates (training sessions & matches), the first column exists of the player names.

I want to create a formula, which automatically gives me an attendance (in percentage).

I tried to calculate the amount of absences with this formula for the first player: =IF(TODAY()>=B1:AB1,COUNTA(B3:AB3),0)

The problem is that only the absences until the current date have to be counted and this formula also includes future absences.

The counted cells should only be the ones until the current date (corresponding to the dates in row 1 of the same column)!

I hope the story is clear enough!

 

[dreid1011]

Can you post a sample of your data with expected results please?

 

[RubenJ]

Can you post a sample of your data with expected results please?

Here's a sample. Of course it is important that the sheet recognises the current date and adjusts automatically.

Current Date: 08/05

Cell B10 (Player A): should show 40% (2 out of 5 present, up until 08/05)
Cell B11 (Player B): Should show 100% (even though there's a future absence)
Cell B12 (Player C): Should show 100%
Cell B13 (Player D): Should show 0%

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Training[/TD]
[TD]08/01[/TD]
[TD]08/02[/TD]
[TD]08/03[/TD]
[TD]08/04[/TD]
[TD]08/05[/TD]
[TD]08/06[/TD]
[TD]08/07[/TD]
[TD]08/08[/TD]
[TD]08/09[/TD]
[TD]08/10[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Player A[/TD]
[TD]Holiday[/TD]
[TD]Holiday[/TD]
[TD]Holiday[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Player B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Work (08/01)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Player C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Player D[/TD]
[TD]Injury[/TD]
[TD]Injury[/TD]
[TD]Injury[/TD]
[TD]Injury[/TD]
[TD]Injury[/TD]
[TD]Injury[/TD]
[TD]Injury[/TD]
[TD]Injury[/TD]
[TD]Injury[/TD]
[TD]Injury[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Attendance[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Player A[/TD]
[TD]B10???[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Player B[/TD]
[TD]B11???[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Player C[/TD]
[TD]B12???[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Player D[/TD]
[TD]B13???[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[RubenJ]

Anyone? Help?

 

[dreid1011]

Does this track on a monthly basis or longer? (new table for each month?) And I am assuming blank cells represent days the player was NOT absent? And are you counting an absence on the current date or only before?

 

[RubenJ]

Does this track on a monthly basis or longer? (new table for each month?) And I am assuming blank cells represent days the player was NOT absent? And are you counting an absence on the current date or only before?

This tracks on a longer basis (max. 6 months within the same calendar year). You are correct in assuming blank cells represent presence. I want to count the absence including the current date.