Absent Counts Based on Attendance

[LOK25]

Hi folks,

I wanted to thank you all in advance for any assistance you may provide. My issue doesn't seem really difficult, but I've been agonizing over this for hours .

Essentially, in addition to the working formula in Cell F2, i realized that i need to perform the same calculation, but based on finding the employee name in Column A.

For example, there are multiple employees in Column A (John Doe and Jane Smith), i need the formula which worked when only one name was present to find all hits of "John Doe" in Column A (A2:A18) then perform the same calculation found in Cell F2 just based on name in column A. Then i could perform the same calculation, but for "Jane Smith" and any other names i add to Column A.

So its a simple count and my correct manual answers are:

-John Doe was absent 5 days and had 6 absent occurrences.
-Jane Smith was absent 4 days and had 5 absent occurrences.

I included the spreadsheet with the previous working solution in Cell F2 when only one employee was involved, but again when i added another employee and expanded the formulas the absences calculation isn't right.

Any help is appreciated.

Updated.xlsx
	A	B	C	D	E	F
1	Employee	Date	Hours Type	Code	Absent Calculation (Working)	Absences Formula #1
2	John Doe	2/1/2021	LA	#	absent	5
3	John Doe	2/2/2021	RF	TS	absent
4	John Doe	2/3/2021	RF	#
5	John Doe	2/4/2021	RF	TS	absent
6	John Doe	2/4/2021	LS	#	absent
7	John Doe	2/8/2021	RF	TS	absent
8	John Doe	2/9/2021	RF	#
9	John Doe	2/10/2021	OS	#	absent
10	Jane Smith	2/1/2021	RF	#
11	Jane Smith	2/2/2021	RF	#
12	Jane Smith	2/3/2021	RF	#
13	Jane Smith	2/4/2021	RF	TS	absent
14	Jane Smith	2/4/2021	LS	#	absent
15	Jane Smith	2/8/2021	RF	TS	absent
16	Jane Smith	2/9/2021	RF	TS	absent
17	Jane Smith	2/10/2021	RF	#
18	Jane Smith	2/11/2021	OS	#	absent
Sheet1

Cell Formulas
Range	Formula
F2	F2	=SUMPRODUCT((IF($E$2:$E$9="absent",1/COUNTIF($B$2:$B$9,$B$2:$B$9),"")))
E2:E18	E2	=IF(OR(LEFT(C2)<>"R",AND(LEFT(C2)="R",LEFT(D2)="T")),"absent","")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F
1	Employee	Date	Hours Type	Code	Absent Calculation (Working)	Absences Formula #1
2	John Doe	02/01/2021	LA	#	absent	5
3	John Doe	02/02/2021	RF	TS	absent
4	John Doe	02/03/2021	RF	#
5	John Doe	02/04/2021	RF	TS	absent
6	John Doe	02/04/2021	LS	#	absent
7	John Doe	02/08/2021	RF	TS	absent
8	John Doe	02/09/2021	RF	#
9	John Doe	02/10/2021	OS	#	absent
10	Jane Smith	02/01/2021	RF	#		4
11	Jane Smith	02/02/2021	RF	#
12	Jane Smith	02/03/2021	RF	#
13	Jane Smith	02/04/2021	RF	TS	absent
14	Jane Smith	02/04/2021	LS	#	absent
15	Jane Smith	02/08/2021	RF	TS	absent
16	Jane Smith	02/09/2021	RF	TS	absent
17	Jane Smith	02/10/2021	RF	#
18	Jane Smith	02/11/2021	OS	#	absent
Result

Cell Formulas
Range	Formula
F2	F2	=SUMPRODUCT((IF((A2:A18=A2)*($E$2:$E$18="absent"),1/COUNTIFS(A2:A18,A2,$B$2:$B$18,$B$2:$B$18),"")))
F10	F10	=SUMPRODUCT((IF((A2:A18=A10)*($E$2:$E$18="absent"),1/COUNTIFS(A2:A18,A10,$B$2:$B$18,$B$2:$B$18),"")))
E2:E18	E2	=IF(OR(LEFT(C2)<>"R",AND(LEFT(C2)="R",LEFT(D2)="T")),"absent","")

 

[LOK25]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F
1	Employee	Date	Hours Type	Code	Absent Calculation (Working)	Absences Formula #1
2	John Doe	02/01/2021	LA	#	absent	5
3	John Doe	02/02/2021	RF	TS	absent
4	John Doe	02/03/2021	RF	#
5	John Doe	02/04/2021	RF	TS	absent
6	John Doe	02/04/2021	LS	#	absent
7	John Doe	02/08/2021	RF	TS	absent
8	John Doe	02/09/2021	RF	#
9	John Doe	02/10/2021	OS	#	absent
10	Jane Smith	02/01/2021	RF	#		4
11	Jane Smith	02/02/2021	RF	#
12	Jane Smith	02/03/2021	RF	#
13	Jane Smith	02/04/2021	RF	TS	absent
14	Jane Smith	02/04/2021	LS	#	absent
15	Jane Smith	02/08/2021	RF	TS	absent
16	Jane Smith	02/09/2021	RF	TS	absent
17	Jane Smith	02/10/2021	RF	#
18	Jane Smith	02/11/2021	OS	#	absent
Result

Cell Formulas
Range	Formula
F2	F2	=SUMPRODUCT((IF((A2:A18=A2)*($E$2:$E$18="absent"),1/COUNTIFS(A2:A18,A2,$B$2:$B$18,$B$2:$B$18),"")))
F10	F10	=SUMPRODUCT((IF((A2:A18=A10)*($E$2:$E$18="absent"),1/COUNTIFS(A2:A18,A10,$B$2:$B$18,$B$2:$B$18),"")))
E2:E18	E2	=IF(OR(LEFT(C2)<>"R",AND(LEFT(C2)="R",LEFT(D2)="T")),"absent","")

Fluff,

Thanks but i found one issue when making a change that i was hoping you could help.

Please notice in Row 5 when i change Code (Column D) from "TS" to "#" which turned "absent" to blank (Column E), this is all correct. However, then the formula in F2 turns to 4.5 absences. Since the employee is still absent for 2/4/2021 this should still show a result of "5" since i cannot have half (.5) of an absence.

I included another snippet below showing the change, thank you so much!

Updated.xlsx
	A	B	C	D	E	F
1	Employee	Date	Hours Type	Code	Absent Calculation (Working)	Absences Formula-John doe
2	John Doe	2/1/2021	LA	#	absent	4.5
3	John Doe	2/2/2021	RF	TS	absent
4	John Doe	2/3/2021	RF	#
5	John Doe	2/4/2021	RF	#
6	John Doe	2/4/2021	LS	#	absent
7	John Doe	2/8/2021	RF	TS	absent
8	John Doe	2/9/2021	RF	#
9	John Doe	2/10/2021	OS	#	absent
Sheet1

Cell Formulas
Range	Formula
F2	F2	=SUMPRODUCT((IF(($A$2:$A$18=A2)*($E$2:$E$18="absent"),1/COUNTIFS($A$2:$A$18,A2,$B$2:$B$18,$B$2:$B$18),"")))
E2	E2	=IF(OR(LEFT(C2)<>"R*",AND(LEFT(C2)="R*",LEFT(D2)="T*")),"absent","")
E3:E9	E3	=IF(OR(LEFT(C3)<>"R",AND(LEFT(C3)="R",LEFT(D3)="T")),"absent","")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Fluff]

Ok, how about

=SUMPRODUCT((IF((A2:A18=A2)*($E$2:$E$18="absent"),1/COUNTIFS(A2:A18,A2,E2:E18,"absent",$B$2:$B$18,$B$2:$B$18),"")))

 

[LOK25]

Ok, how about

=SUMPRODUCT((IF((A2:A18=A2)*($E$2:$E$18="absent"),1/COUNTIFS(A2:A18,A2,E2:E18,"absent",$B$2:$B$18,$B$2:$B$18),"")))

Fluff,

Thanks so much that last post appeared to work.

 

[Fluff]

You're welcome & thanks for the feedback.