# SUMIFS and VLOOKUP combination across sheets; dragging formula

1. ## 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.  Register To Reply

2. ## Re: SUMIFS and VLOOKUP combination across sheets; dragging formula

check the formulas in the attachment  Register To Reply

3. ## 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  Register To Reply

4. ## 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.  Register To Reply

5. ## 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?  Register To Reply

6. ## 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  Register To Reply

7. ## 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.  Register To Reply

8. ## 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.  Register To Reply

9. ## 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?)  Register To Reply

10. ## 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)  Register To Reply