I'm creating a report that references employee information from multiple external workbooks. In these workbooks, there is information in two columns that I am interested in (Active-Inactive and Status). Is there a formula that I can use to count only occurrences where information in the Active-Inactive column="Active" AND information in the Status column="Overdue" (i.e only return a count if an employee shows as "Active" AND "Overdue"?
1 Answers
1
votes
COUNTIFS doesn't work with closed workbooks, you can possibly use SUMPRODUCT like this:
=SUMPRODUCT(([Book1]Sheet1!$A$1:$A$1000="Active")*([Book1]Sheet1!$B$1:$B$1000="Overdue"))
Change workbook/sheet names and column references as required
For data with error values you can use this array formula:
=SUM(IFERROR(([Book1]Sheet1!$A$1:$A$1000="Active")*([Book1]Sheet1!$B$1:$B$1000="Overdue"),0))
confirmed with CTRL+SHIFT+ENTER
easiest to set up with the external workbook open - if you point to the ranges you can get the correct syntax - then when you close external workbook you'll get the full path
