HELP! Is there a way to sum multiple values in a column from a search on another sheet?


New Member
Jul 28, 2010
Hi all.

I am trying to determine the productivity (total hours worked and total througput) for a vertical within a company by summing the various units within that vertical. I'd like to do this without pivot tables as I plan to hand this off for someone to be able to copy and paste in new data only, and have the formulas work.

On the "Reporting" tab I have two dropdown menus configured that will allow the user to select the Vertical and the types of employees (active, retired, etc.) they wish to view and have the hours, throughput and total employees change dynamically with the dropdown. It looks a little like this (it's the same for each week, just different date):

Choose Vertical - Dropdown
Choose Employees - Dropdown

Employees = (functon is sum of all Units in the Vertical)
Total Hours = (function is sum of all Units in the Vertical)
Total Throughput = (function is sum of all Units in the Vertical)
Hourly production = Total Throughput/Total Hours

The data lives on the "Data" sheet and looks something like this

Unit,Vertical,Emp Status,Week Begin,System,Hours,Volume,Employees
1 A,1,All,1/1/10,In,10,250,5
2 A,1,All,1/1/10,Out,6,350,3
3 A,1,Exempt,1/1/10,In,8,150,3
4 A,1,Exempt,1/1/10,Out,4,200,1
5 A,1,NonExempt,1/1/10,In,2,100,2
6 A,1,NonExempt,1/1/10,Out,5,300,2
7 A,1,All,1/8/10,In,9,225,6
8 A,1,All,1/8/10,Out,7,450,5
9 A,1,Exempt,1/8/10,In,6,125,3
10 A,1,Exempt,1/8/10,Out,4,250,3
11 A,1,NonExempt,1/8/10,In,3,100,3
12 A,1,NonExempt,1/8/10,Out,2,200,2
13 B,1,All,1/1/10,In,12,300,6
14 B,1,All,1/1/10,Out,15,500,7
15 B,1,Exempt,1/1/10,In,8,200,3
16 B,1,Exempt,1/1/10,Out,8,200,5
17 B,1,NonExempt,1/1/10,In,4,100,3
18 B,1,NonExempt,1/1/10,Out,7,300,2
19 B,1,All,1/8/10,In,16,650,8
20 B,1,All,1/8/10,Out,9,600,6
21 B,1,Exempt,1/8/10,In,6,225,3
22 B,1,Exempt,1/8/10,Out,5,350,3
23 B,1,NonExempt,1/8/10,In,10,425,5
24 B,1,NonExempt,1/8/10,Out,4,250,3

The end result I'm looking for, once the Vertical and Employee type is chosen on the "Reporting" tab is for the field identified above to be auto-refreshed with the appropriate data, as illustrated below:

Section for those in the system (System=In)
Vertical "1" chosen
Employee Status "All" chosen

Employees = 11 (H1+H13)
Total Hours = 22 (F1+F13)
Total Throughput = 550 (G1+G13)

I have tried using SUMIF, DSUM and even summing VLOOKUP results, but just can't seem to get this to work. I feel like I'm close, but can only get it to return the first week of data, instead of having it sum the various weeks for the various units to equal the Vertical.

Also, as an FYI, I have another tab that calculates the exact same information by Unit (A or B), which is easier because each unit only has those criteria for one week. The issue comes when I want to sum up the various units for the same week to show the whole Vertical (1).

Please help!

Thanks in advance...Darrell.

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
How are you choosing values for the fields other than "Vertical" and type of Employees? I see no field with active or retired as values... is that "system" or "emp status"? Also, how do you choose which date you want?
Upvote 0
excelsupernerd, my apologies. I didn't include all the data I intended in my original post, as it would have been way too much. For the purposes of the question, you may substitute "Exempt" and "NonExempt" for "Active" and "Retired".

As far as the other fields, the "Reporting" tab is divided up in to sections, one for those "In" they system, and one for those who are "Out", as denoted by the "System" data.

The system data information is set us as a simple cell reference, as is the dropdowns for choosing the "Vertical" and "Employee Type"...all on the reporting tab, which is then resolved using a VLOOKUP and/or a concatenated column containing that information. But, like I said, I can't get it to locate each of the weeks (and could be 10 or more) that I need summed up to equal the Vertical's information for that week.

Please let me know if there are more questions that help clarify the situation, as I really need this answered.

Thanks in advance,
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back