Excel 2007 : SUMIFS problem

So this is the problem,

I have over 19 military units which I want to select in a drop down menu.

Each unit has 3 variables which are assigned a category.

What I am trying to do is select different units to forecast total strength.

Unfortunately I can't included the spreadsheet as it is classified.

I have messed around with SUMIF(s), VLOOKUP, and ARRAY and I can't figure out how to plug in different units to give me a total for the combination on each of the three variables.

Hope that was clear enough....Thanks

Can you give us a little information about how your worksheet is structured?

For example, do you have the unit name in column A, variable 1 in column B, var 2 in column C and var 3 in column D, or do you have a long list of unit names with duplicates?

Here is how it is laid out

What I am trying to do is to use the units in the dropdown area. Then numbers stacked in vertical order on sheet three are the same numbers to be summed in vertical order on sheet two. I am looking for the ability to add different units interchangeably to forecast different totals.

Naw that wasn't it. The dropdown area should be holding randomly selected units. the number on sheet three should reflect in the totals on sheet 2.

So to the right of 'units' it should be any sort of combination of the units present.