Dear Mynda,
I have task tracking table attached. can you you help with the formula to fill in sheet 1 the '' open '' or close'' in sheet 2. I want the status of how many open and close for each department.
Thanks for reply
I recommend using a PivotTable for this task as formulas are high maintenance and error prone for tasks like this.
It wasn't clear what you wanted to see, but in the attached file I've inserted a PivotTable on Sheet1. You can add/remove fields to get the desired result.
I hope that points you in the right direction.
Mynda
Good morning Mynda,
Thank you for that, but I want the results for open and closed in sheet 1. any formula to show the results in sheet 1 while keeping the same table in sheet 2.
You can use COUNTIFS for this, but the PivotTable also gave you that information and kept the same table in sheet 2. You can just remove the 'Item' and 'Location' fields from the PivotTable if they're not required.
However, if you insist on a formula you can try this in cell C6 on sheet1:
=COUNTIFS(Sheet2!$H$6:$H$10,Sheet1!$B6,Sheet2!$G$6:$G$10,Sheet1!C$4)
Note: you need to remove the trailing spaces from cells C4 and D4 on Sheet1. Also, the department names on Sheet2 do not match those on Sheet1. You will need to fix your data for the formula to work.
PivotTables don't suffer from these constraints.
Mynda
