+ Reply to Thread
Results 1 to 10 of 10

SUMIFS and VLOOKUP combination across sheets; dragging formula

  1. #1
    Registered User
    Join Date
    05-08-2012
    Location
    New York, NY
    MS-Off Ver
    Excel 2007
    Posts
    10

    SUMIFS and VLOOKUP combination across sheets; dragging formula

    Hi -

    I have attached the workbook containing the problem because this description may be a little confusing. There are 2 sheets, the first is called 'Shopping Lists' and the second is called 'Countries'.

    The 'Shopping Lists' sheet has two sets of data: the bottom set shows each shopping list, each item, the item category, the item country of origin, and the weighting (in %) of that item on the list. The top set is a condensed version of the bottom: it is a simple table with each list, and the composition by category of items in the list.

    The 'Countries' sheet just has a list of countries and whether they are classified as 'Emerging' or 'Developed' economies.

    I have 2 questions and would appreciate any advice or creative solutions (or not so creative...I have a feeling I'm missing some basics):

    1) In Data Set 1, I want to show the composition by category (protein, carb, etc.) for each list. It's easy enough to use SUMIF() for this, but I want to exclude summing items that are internationally sourced. For internationally sourced items, I want to sort them by whether they are from developed or emerging nations.

    I am trying to use a function for these last 2 rows of Data Set 1 that will determine if items are internationally sourced, and then use vlookup within that function to go look on the next sheet ('Countries') to see how that country is classified. I can't figure out this formula...and I don't even know if it's possible to use VLOOKUP() within SUMIFS().

    I don't just want to add a column to Data Set 2 with the classification of the country because then I will have to re-do all the formulas in Data Set 1.

    2) In Data Set 1, I want to use the fill handle to drag the formula across to fill List 2, List 3, List 4, etc. However, when I do this, the fill handle just moves the formula over by 1 cell (because in Data Set 1, each list only takes up a column) - but in Data Set 2 there are multiple columns for each list. How do I copy&paste the formula / use the fill handle so that my formula will use the correct array (i.e. for List 2, the sum_range should be J13:J19, instead of G13:G19)? Can I use OFFSET() for this, and if so, how?

    Thank you VERY much for any help anyone can provide!!! Please let me know if any part of the question is unclear.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor mohd9876's Avatar
    Join Date
    05-04-2011
    Location
    Amman, Jordan
    MS-Off Ver
    Excel 2010
    Posts
    426

    Re: SUMIFS and VLOOKUP combination across sheets; dragging formula

    check the formulas in the attachment
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    05-08-2012
    Location
    New York, NY
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: SUMIFS and VLOOKUP combination across sheets; dragging formula

    Hi - thanks very much for your help. I am trying to figure how you defined the names - I am replicating the "shopping lists" for some financial data and can't figure out how to define the names so they work correctly.

    (I get as far as the COLUMNS() part, but then I don't understand 2 things: why, depending on the cell that is currently selected, the range changes in the Name Manager; and why you have 4-1, 4-2, etc..)

    Thanks again

  4. #4
    Valued Forum Contributor mohd9876's Avatar
    Join Date
    05-04-2011
    Location
    Amman, Jordan
    MS-Off Ver
    Excel 2010
    Posts
    426

    Re: SUMIFS and VLOOKUP combination across sheets; dragging formula

    first of all the name changes depending on the cell that is currently selected is because the I used relative references not absolute, and that will make the name refer to a different column every time you copy the formula over.
    the -1 and -2 are used to find the second to last and third to last columns in the table.

  5. #5
    Registered User
    Join Date
    05-08-2012
    Location
    New York, NY
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: SUMIFS and VLOOKUP combination across sheets; dragging formula

    Thanks for your quick reply - so I know this is a novice question, but when you go to define the name, when you get to the COLUMNS part, what do you exactly type?

  6. #6
    Valued Forum Contributor mohd9876's Avatar
    Join Date
    05-04-2011
    Location
    Amman, Jordan
    MS-Off Ver
    Excel 2010
    Posts
    426

    Re: SUMIFS and VLOOKUP combination across sheets; dragging formula

    I type the value that I want assuming the formula will go in the active cell, so activate the cell that you want to insert the formula in and then type its reference.
    To see what I actually did, go to cell C11 then open the name manager.. there you will see what I actually typed

    Note: I used C11 when I was making the names just for a test, you can use any cell as long as it is in column C
    Last edited by mohd9876; 05-09-2012 at 09:38 AM.

  7. #7
    Registered User
    Join Date
    05-08-2012
    Location
    New York, NY
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: SUMIFS and VLOOKUP combination across sheets; dragging formula

    Thank you - so I just copy pasted all of your definitions, while selecting C4. Now I am trying to input the array formula - I have all the cells (C4:F7) selected, but when I try and input the array formula, it returns the solution in every cell, but each solution is the same and it's the answer that belongs in C4.

  8. #8
    Valued Forum Contributor mohd9876's Avatar
    Join Date
    05-04-2011
    Location
    Amman, Jordan
    MS-Off Ver
    Excel 2010
    Posts
    426

    Re: SUMIFS and VLOOKUP combination across sheets; dragging formula

    select only C4 and then enter the array formula then copy the formula to the other cells or drag the fill handle down and over
    If you select all the cells then enter the array formula it will that your formula return an array which it doesn't, it returns a single value.

  9. #9
    Registered User
    Join Date
    05-08-2012
    Location
    New York, NY
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: SUMIFS and VLOOKUP combination across sheets; dragging formula

    It worked, thanks. Now if I need to add a 5th column to the 'Shopping List' - for example, item color - how does this change the definitions of the names in Name Manager? (i.e., in the name definitions, what does the coefficient 4 correspond to - the number of columns in one shopping list or the number of shopping lists total?)

  10. #10
    Valued Forum Contributor mohd9876's Avatar
    Join Date
    05-04-2011
    Location
    Amman, Jordan
    MS-Off Ver
    Excel 2010
    Posts
    426

    Re: SUMIFS and VLOOKUP combination across sheets; dragging formula

    4 is the number of columns in each list, if you want to add a column just change it to 5 (if you want the last column in the list don't subtract anything if you want the second to last use -1, etc)

+ 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