SUMPRODUCT Based on two tables

[NapB9]

Hello,

I haven't found a similar situation to this on the forums; it may be because my vocabulary on how to describe my question is lacking.

Situation: I am building a dashboard on Excel to summarize staffing data. I have two tables with data in them: one table for Candidates and one table for Jobs. The table for Jobs will contain data specific to the job, such as which division the job is in; the candidates table will have fields such as dates contacted, etc. My goal is to create a third table to summarize how many candidates are in particular divisions. I.e., how many candidates are in the table for Recruitment, Finance, Operations, etc. The problem is that the division field is not in the Candidates Table, and I keep running into problems trying to create a formula to cross reference the two tables.

I've attached a sample spreadsheet - the column in yellow is what I am trying to automate. I have been playing around with SUMPRODUCT (my actual spreadsheet has more criteria in it than just the division, such as Date ranges and other things), INDEX, and MATCH. I know that I could add a calculated field in either the Candidate or Jobs tables to make this much easier (e.g. a VLOOKUP on the candidate table to pull in the division), but the tables will eventually contain thousands of rows of data and I want to avoid putting calculated fields in these tables if possible for fear that the workbook will become too slow over time. I'd prefer to keep the calculation to one formula in a separate area.

Any ideas?

Thanks in advance.