Need help writing complex formula - I'm at my wits end!

[jible diblef]

Hello there, 

I have a problem which I hope someone here can help with...

I want to be able to sum a range of entries from one specific column in another cell in another spreadsheet, on some conditions.

Essentially I have a master staff list which I would like it to be the only one I update, and I want to use a formula in another document which borrows heavily from this list so that it can total entries by itself. 

I've attached an example here. 

I would like the column "Staff Totals" in Table 1 to show how many entries there in table two which match the department, team, and location. 

Please help!

[#2 · Accepted solution]

Hi,

It's not as complex as you think!

Needs to COUNTIFS function.

Put this formula in cell D6: 

=COUNTIFS($G$6:$G$14,A6,$H$6:$H$14,B6,$I$6:$I$14,C6)

Let's suppose that you have two workbooks, the first one called Book1 and the second called Book2.
and Table 1 is located in Book2, Table 2 in Book1.

In this case you can use this formula:

=COUNTIFS([Book1.xlsx]Sheet1!$G$6:$G$14,A5,[Book1.xlsx]Sheet1!$H$6:$H$14,B5,[Book1.xlsx]Sheet1!$I$6:$I$14,C5)

But the problem with COUNTIFS is: it requires that the source file be open.

If you close it, then return to the formula and update it you will get a #VALUE! error!

You have to keep the source file open or to use this formula instead:

=SUMPRODUCT(--(A5=[Book1.xlsx]Sheet1!$G$6:$G$14)*--(B5=[Book1.xlsx]Sheet1!$H$6:$H$14)*--(C5=[Book1.xlsx]Sheet1!$I$6:$I$14)) 

[#3]

Thank you so much! 

Can't wait to get in the office and try your solutions out!