Hello,
Can you help me create a formula for a Countifs that can look at a table then look at the first criteria in a column then whatever row that first criteria is to look for the second criteria and then count it?
I've attached a spreadsheet for more context.
Thank you in advance.
Can you help me create a formula for a Countifs that can look at a table then look at the first criteria in a column then whatever row that first criteria is to look for the second criteria and then count it?
I've attached a spreadsheet for more context.
Thank you in advance.
Example.xlsx | ||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | AG | AH | AI | AJ | AK | AL | |||
1 | January | 2022 | V | Vacation | P | PSL | J | Jury Duty | S | Suspension | UPL | Unpaid Leave | L | Late | D | Disability | FD | Bereavement | LE | Leaving Early | ||||||||||||||||||||
2 | 1 | Sat | Sun | Mon | Tue | Wed | Thu | Fri | Sat | Sun | Mon | Tue | Wed | Thu | Fri | Sat | Sun | Mon | Tue | Wed | Thu | Fri | Sat | Sun | Mon | Tue | Wed | Thu | Fri | Sat | Sun | Mon | ||||||||
3 | Employee Name | PSL | Vacation | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 31 | PSL Taken | Vac Taken | PSL Remaining | Vac Remaining | ||
4 | PARTS - 17000.1 | 300 | 250 | v | p | J | S | L | UPL | D | FD | LE | ||||||||||||||||||||||||||||
5 | LUIS AND CLARK | 0 | 0 | |||||||||||||||||||||||||||||||||||||
6 | JOHN DOE | 0 | 0 | |||||||||||||||||||||||||||||||||||||
7 | JANE DOE | 0 | 0 | |||||||||||||||||||||||||||||||||||||
8 | MAGNUS | 0 | 0 | |||||||||||||||||||||||||||||||||||||
9 | ||||||||||||||||||||||||||||||||||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
D2 | D2 | =TEXT(WEEKDAY(DATE($B$1,$A$2,'C:\Users\wracine\AppData\Local\Microsoft\Windows\INetCache\Content.Outlook\9NVEILC4\2022_Vacation PSL.xlsm'!Table2[[#Headers],[1]]),1),"aaa") |
E2 | E2 | =TEXT(WEEKDAY(DATE($B$1,$A$2,'C:\Users\wracine\AppData\Local\Microsoft\Windows\INetCache\Content.Outlook\9NVEILC4\2022_Vacation PSL.xlsm'!Table2[[#Headers],[2]]),1),"aaa") |
F2 | F2 | =TEXT(WEEKDAY(DATE($B$1,$A$2,'C:\Users\wracine\AppData\Local\Microsoft\Windows\INetCache\Content.Outlook\9NVEILC4\2022_Vacation PSL.xlsm'!Table2[[#Headers],[3]]),1),"aaa") |
G2 | G2 | =TEXT(WEEKDAY(DATE($B$1,$A$2,'C:\Users\wracine\AppData\Local\Microsoft\Windows\INetCache\Content.Outlook\9NVEILC4\2022_Vacation PSL.xlsm'!Table2[[#Headers],[4]]),1),"aaa") |
H2 | H2 | =TEXT(WEEKDAY(DATE($B$1,$A$2,'C:\Users\wracine\AppData\Local\Microsoft\Windows\INetCache\Content.Outlook\9NVEILC4\2022_Vacation PSL.xlsm'!Table2[[#Headers],[5]]),1),"aaa") |
I2 | I2 | =TEXT(WEEKDAY(DATE($B$1,$A$2,'C:\Users\wracine\AppData\Local\Microsoft\Windows\INetCache\Content.Outlook\9NVEILC4\2022_Vacation PSL.xlsm'!Table2[[#Headers],[6]]),1),"aaa") |
J2 | J2 | =TEXT(WEEKDAY(DATE($B$1,$A$2,'C:\Users\wracine\AppData\Local\Microsoft\Windows\INetCache\Content.Outlook\9NVEILC4\2022_Vacation PSL.xlsm'!Table2[[#Headers],[7]]),1),"aaa") |
K2 | K2 | =TEXT(WEEKDAY(DATE($B$1,$A$2,'C:\Users\wracine\AppData\Local\Microsoft\Windows\INetCache\Content.Outlook\9NVEILC4\2022_Vacation PSL.xlsm'!Table2[[#Headers],[8]]),1),"aaa") |
L2 | L2 | =TEXT(WEEKDAY(DATE($B$1,$A$2,'C:\Users\wracine\AppData\Local\Microsoft\Windows\INetCache\Content.Outlook\9NVEILC4\2022_Vacation PSL.xlsm'!Table2[[#Headers],[9]]),1),"aaa") |
M2 | M2 | =TEXT(WEEKDAY(DATE($B$1,$A$2,'C:\Users\wracine\AppData\Local\Microsoft\Windows\INetCache\Content.Outlook\9NVEILC4\2022_Vacation PSL.xlsm'!Table2[[#Headers],[10]]),1),"aaa") |
N2 | N2 | =TEXT(WEEKDAY(DATE($B$1,$A$2,'C:\Users\wracine\AppData\Local\Microsoft\Windows\INetCache\Content.Outlook\9NVEILC4\2022_Vacation PSL.xlsm'!Table2[[#Headers],[11]]),1),"aaa") |
O2 | O2 | =TEXT(WEEKDAY(DATE($B$1,$A$2,'C:\Users\wracine\AppData\Local\Microsoft\Windows\INetCache\Content.Outlook\9NVEILC4\2022_Vacation PSL.xlsm'!Table2[[#Headers],[12]]),1),"aaa") |
P2 | P2 | =TEXT(WEEKDAY(DATE($B$1,$A$2,'C:\Users\wracine\AppData\Local\Microsoft\Windows\INetCache\Content.Outlook\9NVEILC4\2022_Vacation PSL.xlsm'!Table2[[#Headers],[13]]),1),"aaa") |
Q2 | Q2 | =TEXT(WEEKDAY(DATE($B$1,$A$2,'C:\Users\wracine\AppData\Local\Microsoft\Windows\INetCache\Content.Outlook\9NVEILC4\2022_Vacation PSL.xlsm'!Table2[[#Headers],[14]]),1),"aaa") |
R2 | R2 | =TEXT(WEEKDAY(DATE($B$1,$A$2,'C:\Users\wracine\AppData\Local\Microsoft\Windows\INetCache\Content.Outlook\9NVEILC4\2022_Vacation PSL.xlsm'!Table2[[#Headers],[15]]),1),"aaa") |
S2 | S2 | =TEXT(WEEKDAY(DATE($B$1,$A$2,'C:\Users\wracine\AppData\Local\Microsoft\Windows\INetCache\Content.Outlook\9NVEILC4\2022_Vacation PSL.xlsm'!Table2[[#Headers],[16]]),1),"aaa") |
T2 | T2 | =TEXT(WEEKDAY(DATE($B$1,$A$2,'C:\Users\wracine\AppData\Local\Microsoft\Windows\INetCache\Content.Outlook\9NVEILC4\2022_Vacation PSL.xlsm'!Table2[[#Headers],[17]]),1),"aaa") |
U2 | U2 | =TEXT(WEEKDAY(DATE($B$1,$A$2,'C:\Users\wracine\AppData\Local\Microsoft\Windows\INetCache\Content.Outlook\9NVEILC4\2022_Vacation PSL.xlsm'!Table2[[#Headers],[18]]),1),"aaa") |
V2 | V2 | =TEXT(WEEKDAY(DATE($B$1,$A$2,'C:\Users\wracine\AppData\Local\Microsoft\Windows\INetCache\Content.Outlook\9NVEILC4\2022_Vacation PSL.xlsm'!Table2[[#Headers],[19]]),1),"aaa") |
W2 | W2 | =TEXT(WEEKDAY(DATE($B$1,$A$2,'C:\Users\wracine\AppData\Local\Microsoft\Windows\INetCache\Content.Outlook\9NVEILC4\2022_Vacation PSL.xlsm'!Table2[[#Headers],[20]]),1),"aaa") |
X2 | X2 | =TEXT(WEEKDAY(DATE($B$1,$A$2,'C:\Users\wracine\AppData\Local\Microsoft\Windows\INetCache\Content.Outlook\9NVEILC4\2022_Vacation PSL.xlsm'!Table2[[#Headers],[21]]),1),"aaa") |
Y2 | Y2 | =TEXT(WEEKDAY(DATE($B$1,$A$2,'C:\Users\wracine\AppData\Local\Microsoft\Windows\INetCache\Content.Outlook\9NVEILC4\2022_Vacation PSL.xlsm'!Table2[[#Headers],[22]]),1),"aaa") |
Z2 | Z2 | =TEXT(WEEKDAY(DATE($B$1,$A$2,'C:\Users\wracine\AppData\Local\Microsoft\Windows\INetCache\Content.Outlook\9NVEILC4\2022_Vacation PSL.xlsm'!Table2[[#Headers],[23]]),1),"aaa") |
AA2 | AA2 | =TEXT(WEEKDAY(DATE($B$1,$A$2,'C:\Users\wracine\AppData\Local\Microsoft\Windows\INetCache\Content.Outlook\9NVEILC4\2022_Vacation PSL.xlsm'!Table2[[#Headers],[24]]),1),"aaa") |
AB2 | AB2 | =TEXT(WEEKDAY(DATE($B$1,$A$2,'C:\Users\wracine\AppData\Local\Microsoft\Windows\INetCache\Content.Outlook\9NVEILC4\2022_Vacation PSL.xlsm'!Table2[[#Headers],[25]]),1),"aaa") |
AC2 | AC2 | =TEXT(WEEKDAY(DATE($B$1,$A$2,'C:\Users\wracine\AppData\Local\Microsoft\Windows\INetCache\Content.Outlook\9NVEILC4\2022_Vacation PSL.xlsm'!Table2[[#Headers],[26]]),1),"aaa") |
AD2 | AD2 | =TEXT(WEEKDAY(DATE($B$1,$A$2,'C:\Users\wracine\AppData\Local\Microsoft\Windows\INetCache\Content.Outlook\9NVEILC4\2022_Vacation PSL.xlsm'!Table2[[#Headers],[27]]),1),"aaa") |
AE2 | AE2 | =TEXT(WEEKDAY(DATE($B$1,$A$2,'C:\Users\wracine\AppData\Local\Microsoft\Windows\INetCache\Content.Outlook\9NVEILC4\2022_Vacation PSL.xlsm'!Table2[[#Headers],[28]]),1),"aaa") |
AF2 | AF2 | =TEXT(WEEKDAY(DATE($B$1,$A$2,'C:\Users\wracine\AppData\Local\Microsoft\Windows\INetCache\Content.Outlook\9NVEILC4\2022_Vacation PSL.xlsm'!Table2[[#Headers],[29]]),1),"aaa") |
AG2 | AG2 | =TEXT(WEEKDAY(DATE($B$1,$A$2,'C:\Users\wracine\AppData\Local\Microsoft\Windows\INetCache\Content.Outlook\9NVEILC4\2022_Vacation PSL.xlsm'!Table2[[#Headers],[30]]),1),"aaa") |
AH2 | AH2 | =TEXT(WEEKDAY(DATE($B$1,$A$2,'C:\Users\wracine\AppData\Local\Microsoft\Windows\INetCache\Content.Outlook\9NVEILC4\2022_Vacation PSL.xlsm'!Table2[[#Headers],[31]]),1),"aaa") |
B4:B8 | B4 | =SUMIF('C:\Users\wracine\AppData\Local\Microsoft\Windows\INetCache\Content.Outlook\9NVEILC4\[2022_Vacation PSL.xlsm]Main'!B:B,'C:\Users\wracine\AppData\Local\Microsoft\Windows\INetCache\Content.Outlook\9NVEILC4\2022_Vacation PSL.xlsm'!Table2[@[Employee Name]],'C:\Users\wracine\AppData\Local\Microsoft\Windows\INetCache\Content.Outlook\9NVEILC4\[2022_Vacation PSL.xlsm]Main'!C:C) |
C4:C8 | C4 | =SUMIF('C:\Users\wracine\AppData\Local\Microsoft\Windows\INetCache\Content.Outlook\9NVEILC4\[2022_Vacation PSL.xlsm]Main'!B:B,'C:\Users\wracine\AppData\Local\Microsoft\Windows\INetCache\Content.Outlook\9NVEILC4\2022_Vacation PSL.xlsm'!Table2[@[Employee Name]],'C:\Users\wracine\AppData\Local\Microsoft\Windows\INetCache\Content.Outlook\9NVEILC4\[2022_Vacation PSL.xlsm]Main'!D:D) |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
P4 | Cell Value | beginning with "L" | text | NO |
F4:X4 | Cell Value | contains "LE" | text | NO |
F4:X4 | Cell Value | contains "FD" | text | NO |
F4:X4 | Cell Value | contains "D" | text | NO |
F4:X4 | Cell Value | contains "UPL" | text | NO |
F4:P4,R4:X4 | Cell Value | contains "S" | text | NO |
F4:P4,R4:X4 | Cell Value | contains "J" | text | NO |
F4:P4,R4:X4 | Cell Value | contains "P" | text | NO |
F4:P4,R4:X4 | Cell Value | contains "B" | text | NO |
F4:P4,R4:X4 | Cell Value | contains "V" | text | NO |
D4:E4,Y4:AH4,D5:AH8 | Cell Value | contains "LE" | text | NO |
D4:E4,Y4:AH4,D5:AH8 | Cell Value | contains "FD" | text | NO |
D4:E4,Y4:AH4,D5:AH8 | Cell Value | contains "D" | text | NO |
D4:E4,Y4:AH4,D5:AH8 | Cell Value | contains "UPL" | text | NO |
D5:O5,Q5:AH5,Y4:AH4,D4:E4,D6:AH8 | Cell Value | contains "S" | text | NO |
D5:O5,Q5:AH5,Y4:AH4,D4:E4,D6:AH8 | Cell Value | contains "J" | text | NO |
D5:O5,Q5:AH5,Y4:AH4,D4:E4,D6:AH8 | Cell Value | contains "P" | text | NO |
D5:O5,Q5:AH5,Y4:AH4,D4:E4,D6:AH8 | Cell Value | contains "B" | text | NO |
D5:O5,Q5:AH5,Y4:AH4,D4:E4,D6:AH8 | Cell Value | contains "V" | text | NO |