Formula doesnt work ...

[Chloe1113]

=SUMIFS(INDEX('SKU REPORT'!$D:$AG,,MATCH($J$2&M1&P2,'SKU REPORT'!$D$4:$AG$4&'SKU REPORT'!D2:AG2&'SKU REPORT'!D3:AG3,0)),'SKU REPORT'!B:B,DATA!A:A,'SKU REPORT'!A:A,DATA!K:K)

Why does this not work???? Please help!

 

[Gerald Higgins]

Hmm . . looks complicated

Let's try breaking it into chunks.

What is this bit trying to do ?
MATCH($J$2&M1&P2,'SKU REPORT'!$D$4:$AG$4&'SKU REPORT'!D2:AG2&'SKU REPORT'!D3:AG3,0)

Don't worry too much about the real world application of your situation, that might not be too relevant, just focus on what data you have and explaining what exactly you are trying to do with it.

 

[Gerald Higgins]

And it looks like all of this is related to this other thread ?
https://www.mrexcel.com/forum/excel-questions/1100032-sumif-index-match-multiple.html

I'll post a reply in THAT thread pointing people at this one.
It's best not to start multiple posts on the same topic.

 

[Chloe1113]

Hi thanks so much for replying - i need to write a formula which says : if in tab 2 you can match cells in 3 different rows then sum that column if it also matches 2 columns - does that make sense? The match bit specifically is trying to say If you can match cells j2 and m1 and p2 in the three ranges on my second tab then do the sumif

 

[Gerald Higgins]

OK. The three ranges on your second tab are continuous, so you can POSSIBLY refer to them as a single range - D2:AG4.

I say "POSSIBLY" because you have used absolute cell references ($ symbols) for one range and relative cell references for the other two, which makes me think perhaps you want to treat them separately.
But I'm guessing the mix of absolute and relative references may have been a mistake on your part - if so, I would just use the single range reference.

I would then ask - where in this range might you find j2&m1&p2 ?
Is it only in column D ?
Or is it anywhere in the target range ?

 

[Chloe1113]

Hi it isnt only in column D its in the range

 

[Gerald Higgins]

OK well I think that's going to be a problem then, because I THINK MATCH can only work with a one dimensional range, such as
D2:AG2
OR
D2:D4
but not
D2:AG4

You see the difference ?

 

[Chloe1113]

Ahh ok so basically this is my table on the back up sheet

column a - week
column b is barcode

They are my sumifs (which is fine and working i think)

Row 2 says if it is units or value
row 3 says which country it is
row 4 says if it in online or offline

so my data starts from week down (below) and above my table i have the following

online offline online offline
uk uk ireland ireland
week barcode units sales units sales
22 xxxxx 1 10 2 20

i want to say if it says online and uk and unit and matches barcode and matches the week return the result

 

[Gerald Higgins]

OK, I'm not sure how post #8 relates to post #1 .

Can you post a small sample of your data (make it up if you don't want to show real data), and describe as clearly as you can, what you want to do with it exactly.

Again, don't worry too much about explaining the real world application of your situation, that might not be too relevant, just focus on what data you have and explaining what exactly you are trying to do with it.