+ Reply to Thread
Results 1 to 9 of 9

Excel 2007 : SUMIFS problem

  1. #1
    Registered User
    Join Date
    08-24-2011
    Location
    Las Vegas, NV
    MS-Off Ver
    Excel 2010
    Posts
    4

    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. #2
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    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. #3
    Valued Forum Contributor
    Join Date
    05-23-2011
    Location
    Lahore PK
    MS-Off Ver
    Excel 2007, 2013
    Posts
    627

    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
    Azam
    If you want to say Thank you to a member, click the reputation icon (Star) in the left bottom of the post.
    For prompt answer, be descriptive, concise, short, direct, and to-the-point.

  4. #4
    Registered User
    Join Date
    08-24-2011
    Location
    Las Vegas, NV
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: SUMIFS problem

    Here is how it is laid out
    Attached Files Attached Files

  5. #5
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    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?
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  6. #6
    Registered User
    Join Date
    08-24-2011
    Location
    Las Vegas, NV
    MS-Off Ver
    Excel 2010
    Posts
    4

    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. #7
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    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:

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

    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.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    08-24-2011
    Location
    Las Vegas, NV
    MS-Off Ver
    Excel 2010
    Posts
    4

    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. #9
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    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...
    Last edited by NBVC; 08-29-2011 at 04:17 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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