SUMIFS with multiple criteria on a different tab

[tdbarber]

I am trying to extract the numbers from my data tab on the attachment. I need Excel to first look up the store number in the data tab, like 4201-DE4201 in A3, then look for the number 31 in column A but just under 4201-DE4201 and bring back the data in column E back to the sales tab. I have 2 helper columns in the sales tab to use for the 2 lookup items.

[Richard Buttrey]

Before deciding the best approach it would help and simplify things if the Store number was on every row in a new first column in the Data sheet, or better still the store number concatenated with the current numeric in column A
e.g.

4201-DE4201_1
4201-DE4201_2
4201-DE4201_25

Are you able/willing to make this small change.

What's the significance of the number 31 and why are you only interested in #31?
To where in the Sales sheet do you want to bring the Data Column E value?

[Glenn Kennedy]

The data layout in Data is pretty horrible... but this might do the job:

=IFERROR(INDEX(Data!$E$3:$E$600,MATCH(Sales!K3,Data!$A$3:$A$600,0)+MATCH(J3,Data!$A$4:$A$37,0)),"")

in sales, L3, copied down. If not, please don't show us a non-working formula, show us a few rows of manually calculated results...

[Glenn Kennedy]

BtW, my approach will ONLY work if the subheading numbers (31, etc) are ALWAYS the same for each of these things: 4201-DE4201

If the reality is different, then your data layout in data is REALLY horrible... and needs to be changed.

[Richard Buttrey]

I agree Glenn.

The numbers in Sales column A are not consistent from Shop to Shop and as you say the data layout is far from ideal for analysis.

[tdbarber]

The data sheet comes like that. I copied and pasted the data from a different file. The number 31 is under each store number and column E across from 31 is the data I need. I will be replicating the formula for the rest of the stores in the list. I have not inputted their info in the 2 helper columns yet. I am just starting with 1 store to figure out the formula first. Under each store number there are quite a few different data items. The data items are numbered under each store and the number corresponds to the products in column B. The dollar amounts in column E is what I need for each individual store.

[Richard Buttrey]

OK,

Then insert a NEW column A in the Data and in A4 enter

Please Login or Register  to view this content.

and copy it down.

Then in whichever column of the Sales sheet you want to record the Labour figure add in row 3 copied down

Please Login or Register  to view this content.

[tdbarber]

The 2nd formula came back with #N/A instead of a result. I looked and I know there is a result for that store. I have attached the 2 spreadsheets created with the 2 options given by different people. The file I receive is what it is and I have to make do with it somehow.

[tdbarber]

I did this and it worked for some but not all. I know the info on the data tab is very mixed up but that is how I receive it.

[Richard Buttrey]

TI have attached the 2 spreadsheets created with the 2 options given by different people. The file I receive is what it is and I have to make do with it somehow.

Hi,

The files weren't attached...

[tdbarber]

I was out of the office yesterday and have now attached the files

[Glenn Kennedy]

If it's the labour costs, from column F:

=VLOOKUP(K3&"_"&J3,Data!$A:$F,6,FALSE)

Richard's approach was correct.. but for the omission of ,FALSE

[tdbarber]

That worked perfectly! Thanks to all!

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.