Countifs with table and multiple criteria

[nawdrizzy]

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.

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

 

[jasonb75]

Maybe

=COUNTIF(D4:AH4,"p")

As you simply refer to 'criteria' without specifying what the said criteria are or what they should be compared to, a lot is open to interpretation.

 

[nawdrizzy]

Sorry about that. From my example sheet what I want to count is "LE". If for example "John Doe" is anywhere in column A, I want it to evaluate that row in look for "LE" and count how many there are.

Hope that clears it up a bit.

 

[jasonb75]

Something like

=COUNTIF(INDEX($D$4:$AH$8,MATCH("John Doe",$A$4:$A$8,0),0),"LE")

Replace the text criteria with cell references as required and add in sheet names as needed.

 

[nawdrizzy]

This works if I'm keeping the results on the same sheet. How can I get it to work on a separate sheet?

Example.xlsx
	A	B
1	Employee Name	Leaving Early
2	PARTS - 17000.1
3	LUIS AND CLARK
4	JOHN DOE
5	JANE DOE
6	MAGNUS
7
Sheet2

 

[jasonb75]

How can I get it to work on a separate sheet?

As I already said

add in sheet names as needed.

 

[nawdrizzy]

Thank You