Forum Discussion
Need help writing complex formula - I'm at my wits end!
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))
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!
