CODEWITHSUNDEEP
excel
Komarov
Komarov

Reputation: 35

COUNTIF + MATCH WITH DATA

I've got a problem and can't really figure it out the formula. I really hope You gonna understand what I'm trying to say.

I have a sheet which is called: Leave Tracker, in Row 7 i've got dates. enter image description here

Then i have a second sheet which is called: Daily Staffing. The formula showed below counting me how many 'FT' codes are in column that matches with DailyStaffing B2.

{=COUNTIF(INDEX('Leave Tracker'!$B$9:$NI$485,0,MATCH($B$2,'Leave Tracker'!$B$7:$NI$7,0)),"FT")}

enter image description here Then i have a 3rd sheet thats called Employee Data, where i have all data of my users. enter image description here

So i've figured it out how to count how many users are matching criteria (date and code "FT"), but I need to add a second criteria which is gonna be "CORE" from sheet Employee Data.

Any ideas how to solve it? I was trying to add a second INDEX,MATCH but as an output im getting only REF or SPILL.

Thank You all in advance! :)

Upvotes: 0

Views: 148

Answers (1)

Matt Drake
Matt Drake

Reputation: 156

Try this...

Adjust 'Leave Tracker'!$A$9:$A$20 and 'Employee Data'!$A$2:$H$20 to your full range of users

=SUMPRODUCT(('Leave Tracker'!$B$7:$NI$7=$B$2)*('Leave Tracker'!$B$9:$NI$485="FT")*(LOOKUP('Leave Tracker'!$A$9:$A$20,'Employee Data'!$A$2:$H$20)="core"))

Upvotes: 1

Related Questions