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.
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. |