Forum Discussion
Need help writing complex formula - I'm at my wits end!
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!
Hi,
It's not as complex as you think!
Needs to https://support.office.com/en-us/article/countifs-function-dda3dc6e-f74e-4aee-88bc-aa8c2a866842 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))
2 Replies
Hi,
It's not as complex as you think!
Needs to https://support.office.com/en-us/article/countifs-function-dda3dc6e-f74e-4aee-88bc-aa8c2a866842 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))
Thank you so much!
Can't wait to get in the office and try your solutions out!
