developing a better calculating method

Ok. I'm going to try and explain what I'm trying to do.

I have a spreadsheet which contains an attendance report of all bodies in the multiple departments within my command.

The spreadsheet allows users to select someone from column D and change their location from one department to another. As you can see in the example, I clicked the drop down box to show the departments they can be moved to.

When I select an individual and move them to the appropriate new department (and hit the sort button which in turn sorts the name by w-n and r-n order), I need all the cells in red from column H to show the correct value.

The red cells in column H (5 rows) are in essence the same as the values in H14, H33, and H65 FOR THAT DAY.(notice each column is a different day)

Basically the totals on the top of the sheet must be captured in a more specific level, by department, in the mirroring red cells.

The help I need is this: I need to figure out what or how to find the correct formula to place in H14, H15, H16 that will capture the totals, while also considering that people are moving constantly back and forth!

Please note:

W-N and R-N numbers are determined on a hidden data sheet. when clicking the "add a person" button, it creates a new line and allows the user to click a drop down box and assign a Work Center (once that happens, a w-n number is assigned to it, which is determined on the hidden data sheet). After assigning the rank and last name of the person, clicking the sort button places it into the correct work center, and in the correct rank order.

"x"'s are people who are on the island of Japan. "o"s are people off the island.

If anyone needs more explanation, I am available to email the spreadsheet.

Hi,

I've not grasped all your rather complicated s/s but I can't help thinking that a Pivot table is not a better way to proceed.

HTH