Forum

Notifications
Clear all

tracking table

4 Posts
2 Users
0 Reactions
91 Views
(@inboxer)
Posts: 4
Active Member
Topic starter
 

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 

 
Posted : 02/11/2019 8:00 am
(@mynda)
Posts: 4761
Member Admin
 

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

 
Posted : 02/11/2019 11:02 pm
(@inboxer)
Posts: 4
Active Member
Topic starter
 

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.

 
Posted : 02/11/2019 11:20 pm
(@mynda)
Posts: 4761
Member Admin
 

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

 
Posted : 03/11/2019 2:44 am
Share: