# Excel 2007 : SUMIFS problem

1. ## 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

2. ## Re: SUMIFS problem

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?

3. ## Re: SUMIFS problem

You may upload a sample workbook with small sample data and your desired results for further explanation

In order to attach a file Click the Go Advanced button, and click on the Manage Attachments button

4. ## Re: SUMIFS problem

Here is how it is laid out

5. ## Re: SUMIFS problem

So are you going to have 19 totals too in Sheet2?

What exactly is totalled? The whole rows in Sheet3?

Can one pick the same item multiple times? If so, are you summing those multiples?

6. ## Re: SUMIFS problem

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.

7. ## Re: SUMIFS problem

You're not making it easy to understand your quest. I have take a guess with the attached. See if it is what you desired.

The data validation drop down list are created by selecting B3:B9 on Sheet2, going to Data menu, Data Validation, then select List from Allow list and enter the source range (e.g. =Sheet3!L2:L4)

then the totals are in B10:

=SUM(Sheet3!\$A\$2:\$J\$2)*COUNTIF(Sheet2!\$B\$3:\$B\$9,"MTOE")

in B11:

=SUM(Sheet3!\$A\$2:\$J\$2)*COUNTIF(Sheet2!\$B\$3:\$B\$9,"AGR")

in B12:

These some all the columns on Sheet3 for the each specific row item and multiplies by the number of times that item is chosen in the drop down lists.

8. ## Re: SUMIFS problem

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.

9. ## Re: SUMIFS problem

Like I said...

You're not making it easy to understand your quest
Maybe actual expected results and reasoning would be beneficial to all...

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1