conditional count based on lookups

[Chets]

I have a spreadsheet with 3 tabs - Hiring, Staff, and Metric. Sample uploaded.

- The Hiring tab includes a list of all jobs that are or have been hired with fill date and person who got the job.

- Staff tab includes list of all staff in the organization.

- Metric tab - here I would like to count all the jobs that get filled with the month being reported and person is a new to the organization. As an example, the March 2017 metric shows that 2 jobs were filled. I would like to extend the formula to only count if the person's name (from Hiring tab) is NOT in the Staff list.

How do I do this using formula (no macros).

Thanks.

 

[Peter Bartholomew]

I would add a helper field to your 'hiring' table to identify existing staff.
= COUNTIFS( Staff[Last], [@Last], Staff[First], [@First] )

 

[bosco_yip]

Multi-conditional count should be switched to SUMPRODUCT()

In "metric" sheet B2, copied across :

=SUMPRODUCT((Hiring!$B2:$B22<=EOMONTH(B$1,0))*(Hiring!$B2:$B22>=B$1)*(Hiring!$C$2:$C$22<>"")*(0+ISNUMBER(MATCH(Hiring!$C$2:$C$22&Hiring!$D$2:$D$22,Staff!$A$2:$A$22&Staff!$B$2:$B$22,0))=0))

Regards
Bosco