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

[Road_Warrior]

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

1/1/10
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

A,B,C,D,E,F,G,H
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:

1/1/10
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.

 

[excelsupernerd]

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?

 

[Road_Warrior]

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,
Darrell