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

Report •

#1
April 29, 2019 at 17:14:31
✔ Best Answer
1st Issue: The #NAME error is happening because you have the syntax wrong. You need single quotes around the Sheet names.

=COUNTIFS('Work Orders'!A:A,$A$2,'Work Orders'!B:B,A5)

2nd Issue: =AVERAGEIFS(...

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Report •

#2
April 30, 2019 at 10:13:14
Thank you! I don't know how I missed that! Everything is working the way it should now.

And AVERAGEIFS is a much better solution than what I was thinking. Thanks for the tip!


Report •
Related Solutions


Ask Question