Solved Using COUNTIFS Across Multiple Worksheets

Microsoft Excel 2016 suites
April 29, 2019 at 15:37:20
Specs: Windows 10
 Good afternoon,I am trying to look at work orders completed at several retail shopping centers. I need to be able to count how many work orders there are and calculate the average time it took for them to be completed. I have one worksheet (called "Work Orders") that has a list of all the work orders, which property it was at, the job type, and how long it took to complete. On the next worksheet (called "Stats"), I have a cell with a drop-down list (using Data Validation) that includes the name of each property. Below that I have a list of the Job Types with a column for "Total Work Orders" and another for "Average Completion Time" next to it. I am wanting to be able to select a property from the drop-down list, and have the "Total Work Orders" and "Average Completion Time" automatically filled for each job type. Work Orders worksheet: ``` A B C 1 Property Name Job Type Days to Complete 2 Property 1 Security 3 3 Property 2 Janitorial 5 4 Property 3 Landscaping 10```Stats Worksheet:``` A B C 1 Property: 2 Property 1 3 4 Job Type Total Work Orders Average Completion Time 5 Janitorial 6 Landscaping 7 Security```I have tried "=COUNTIFS(Work Orders!A:A,\$A\$2,Work Orders!B:B,A5)" in B5 on the "Stats" worksheet, but I'm just getting the #NAME? error. I haven't tried a formula for the Average Completion Time yet, because I need to get this one fixed first. But I expect I may need to use VLOOKUP in it somewhere.I've used COUNTIFS in the past, but not between two worksheets. It looks like that is what is causing the error, but I'm honestly not sure. It looks to me like this should work. Any help would be appreciated. Thank you!message edited by tgordon21

See More: Using COUNTIFS Across Multiple Worksheets

#1
April 29, 2019 at 17:14:31