Making a running 365 absence tracker

[HorizonHunter]

Building a staff absence tracker for easier management. Column A is names. Column B is a running total. Column C is the date of an absence and Column D is the reason (excused or not). Column C and D essentially repeat for each subsequent absence a person has. I am using data validation to standardize the excuse options.

I'm currently using COUNT in Column B to count all numbers in the row, but that is returning excused absences as well. Ideally it would check each excuse column (D), and if not excused, count that date (C) towards the total (B).

Second complication is making it a running year count. So if someone has an absence on 11/2/2022, it would no longer be counted 11/2/2023.

Third complication is Tardy, which every 3 count as 1 absence.

Name	Total	Date	Reason	Date	Reason
John	1	12/15/22	Sick
Mary	0	11/6/22	Excused
Sue	0	10/31/22	Sick
Dave	1	12/22/22	Sick	12/3/22	Tardy

I've looked in to DCOUNT, INDEX, MATCH, and VLOOKUP, but either I'm implementing them wrong, or I need to reconsider the current layout of the sheet.

 

[awoohaw]

How about:

(This doesn't take the 3*Tardys into consideration, sorry)

Mr Excel Questions 71.xlsm
	A	B	C	D	E	F
1	Name	Total	Date	Reason	Date	Reason
2	John	1	2022-12-15	Sick
3	Mary	0	2011-06-22	Excused
4	Sue	1	2022-10-31	Sick
5	Dave	2	2022-12-22	Sick	2012-03-22	Tardy
6	Bill	1	2022-12-23	Excused	2012-03-23	Tardy
HorizonHunter

Cell Formulas
Range	Formula
B2:B6	B2	=SUM(ISEVEN(COLUMN(D2:F2))*(D2:F2<>"Excused")*(ISBLANK(D2:F2)=FALSE))

 

[awoohaw]

Try this, I think it uses all 3 criteria. But, I do strongly agree with you that your layout is problematic as you will always need to adjust your formulas.
If you input data into 3 columns (Name, Date, Excuse) you can develop PIVOT tables and other Summarizations more easily.

Mr Excel Questions 71.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Name	Total	Date	Reason	Date	Reason	Date	Reason	Date	Reason	Date	Reason
2	John	1	2022-12-15	Sick
3	Mary	0	2011-06-22	Excused
4	Sue	1	2022-10-31	Sick
5	Dave	3	2022-12-22	Sick	2022-03-22	Tardy	2022-03-22	Sick	2022-03-22	Sick	2022-03-22	Tardy
6	Bill	1	2022-12-23	Excused	2022-03-23	Tardy	2023-03-23	Tardy	2022-03-23	Tardy	2022-03-23	Tardy
7	Ron	1	2022-12-24	Excused	2022-03-24	Sick	2023-03-24	Tardy	2022-03-24	Tardy	2022-03-24	Tardy
HorizonHunter

Cell Formulas
Range	Formula
B2:B7	B2	=SUM(ISEVEN(COLUMN(D2:L2))*(D2:L2<>"Excused")*(D2:L2<>"Tardy")*(ISBLANK(D2:L2)=FALSE) *ISODD(COLUMN(C2:K2))*IFERROR(YEAR(C2:K2)=2022,0)) + INT(SUM((IFERROR(YEAR(C2:K2)=2022,0))*(D2:L2="Tardy"))/3)

 

[HorizonHunter]

Try this, I think it uses all 3 criteria. But, I do strongly agree with you that your layout is problematic as you will always need to adjust your formulas.
If you input data into 3 columns (Name, Date, Excuse) you can develop PIVOT tables and other Summarizations more easily.

I'm gonna try this later after I wrap my head around it. Request comes from my tech illiterate spouse, so trying to make her job easier, using the format she's used to.