Countifs with table and multiple criteria

nawdrizzy

New Member
Joined
Aug 13, 2018
Messages
18
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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKAL
1January2022VVacationPPSLJJury DutySSuspensionUPLUnpaid Leave LLate DDisabilityFDBereavementLELeaving Early
21SatSunMonTueWedThuFriSatSunMonTueWedThuFriSatSunMonTueWedThuFriSatSunMonTueWedThuFriSatSunMon
3Employee NamePSLVacation12345678910111213141516171819202122232425262728293031PSL TakenVac TakenPSL Remaining Vac Remaining
4PARTS - 17000.1300250vpJSLUPLDFDLE
5LUIS AND CLARK00
6JOHN DOE 00
7JANE DOE00
8MAGNUS 00
9
Sheet1
Cell Formulas
RangeFormula
D2D2=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")
E2E2=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")
F2F2=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")
G2G2=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")
H2H2=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")
I2I2=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")
J2J2=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")
K2K2=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")
L2L2=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")
M2M2=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")
N2N2=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")
O2O2=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")
P2P2=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")
Q2Q2=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")
R2R2=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")
S2S2=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")
T2T2=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")
U2U2=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")
V2V2=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")
W2W2=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")
X2X2=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")
Y2Y2=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")
Z2Z2=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")
AA2AA2=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")
AB2AB2=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")
AC2AC2=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")
AD2AD2=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")
AE2AE2=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")
AF2AF2=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")
AG2AG2=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")
AH2AH2=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:B8B4=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:C8C4=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
CellConditionCell FormatStop If True
P4Cell Valuebeginning with "L"textNO
F4:X4Cell Valuecontains "LE"textNO
F4:X4Cell Valuecontains "FD"textNO
F4:X4Cell Valuecontains "D"textNO
F4:X4Cell Valuecontains "UPL"textNO
F4:P4,R4:X4Cell Valuecontains "S"textNO
F4:P4,R4:X4Cell Valuecontains "J"textNO
F4:P4,R4:X4Cell Valuecontains "P"textNO
F4:P4,R4:X4Cell Valuecontains "B"textNO
F4:P4,R4:X4Cell Valuecontains "V"textNO
D4:E4,Y4:AH4,D5:AH8Cell Valuecontains "LE"textNO
D4:E4,Y4:AH4,D5:AH8Cell Valuecontains "FD"textNO
D4:E4,Y4:AH4,D5:AH8Cell Valuecontains "D"textNO
D4:E4,Y4:AH4,D5:AH8Cell Valuecontains "UPL"textNO
D5:O5,Q5:AH5,Y4:AH4,D4:E4,D6:AH8Cell Valuecontains "S"textNO
D5:O5,Q5:AH5,Y4:AH4,D4:E4,D6:AH8Cell Valuecontains "J"textNO
D5:O5,Q5:AH5,Y4:AH4,D4:E4,D6:AH8Cell Valuecontains "P"textNO
D5:O5,Q5:AH5,Y4:AH4,D4:E4,D6:AH8Cell Valuecontains "B"textNO
D5:O5,Q5:AH5,Y4:AH4,D4:E4,D6:AH8Cell Valuecontains "V"textNO
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Maybe
Excel Formula:
=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.
 
Upvote 0
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.
 
Upvote 0
Something like
Excel Formula:
=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.
 
Upvote 0
Solution
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
AB
1Employee NameLeaving Early
2PARTS - 17000.1
3LUIS AND CLARK
4JOHN DOE
5JANE DOE
6MAGNUS
7
Sheet2
 
Upvote 0

Forum statistics

Threads
1,223,229
Messages
6,170,881
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top