Sumif Formula Not working

[eliturk89]

Hey guys,

I have a problem and I have no idea why it is happening. I am using a Sumif formula shown below in the photos. It looks through a different sheet for specific SKU's then multiplies them by the amount needed per part. I don't know why, but it is pulling sku's that are almost the same instead of the correct one. an Example is that it should be pulling SKU 5006-000177-22 instead it's telling me I need to order 5006-000177-5. I thought you guys might have any idea why this is happening or if you had a better way to scrub for the info. Unfortunately, I couldn't upload a sample because the sample is working correctly, and I don't know how to recreate the issue. The original file is too large for the forum to let me download it.


Formula:
Formula.PNG

What it is scrubbing from:
Details.PNG

Inventory Quantity_12152022.zip

[Glenn Kennedy]

Save it as a zip, or as an xlsb... or prepare a SAMPLE, not the whole thing.

[eliturk89]

Save it as a zip, or as an xlsb... or prepare a SAMPLE, not the whole thing.

I had tried to make the sample, but in the sample it was working. Thank you for suggesting the ZIP. IT worked and is uploaded.

[Glenn Kennedy]

omg.

Why do you think the SUMIF is incorrect. Its looking up the value in A1021 (5006-000177-5) and returning the correct value from cell OF37. I checked by cop/paste value the array in OD2:OF39 and changed the 1 to 11. The SUMIF worked OK.

So... WHY is it incorrect?

[eliturk89]

omg.

Why do you think the SUMIF is incorrect. Its looking up the value in A1021 (5006-000177-5) and returning the correct value from cell OF37. I checked by cop/paste value the array in OD2:OF39 and changed the 1 to 11. The SUMIF worked OK.

So... WHY is it incorrect?

I see what you mean, OF37 is supposed to be A131 from "Input Page" which shown below as QTY: 12, it is somehow multiplying it by the number 2 rows down of 1792 on A133 of the "Input" sheet. I don't know why it would do that. So im guessing something is shifted over or something that is making it count the incorrect column.

Input page:
Attachment 811191

[Fluff13]

You are looking at the wrong column for col EC (probably the others). The formula is looking at OE when I suspect it should be looking at OK

[eliturk89]

You are looking at the wrong column for col EC (probably the others). The formula is looking at OE when I suspect it should be looking at OK

Which formula is looking at OE?

[Glenn Kennedy]

Why column OK, Fluff?

[Fluff13]

Because OJ2 on the Details sheet is pulling from A133 on the input page sheet & B133 matches the code in EC1 on the quantity sheet

[Glenn Kennedy]

EC 1021 is looking at OE.

[eliturk89]

EC 1021 is looking at OE.

Yeah, I still don't understand why it's doing that. I need to rethink how it's scrubbing the part numbers I think. The formula is working for some but not others correctly.

[Glenn Kennedy]

I think it's the minus 2 that's throwing it out...

=EC$2*SUMIF(OFFSET(Details!$B:$B,,COLUMN()*3),$A1019,OFFSET(Details!$C:$C,,COLUMN()*3))

[eliturk89]

I think it's the minus 2 that's throwing it out...

=EC$2*SUMIF(OFFSET(Details!$B:$B,,COLUMN()*3),$A1019,OFFSET(Details!$C:$C,,COLUMN()*3))

It didn't change it. Not sure, what I'm going to do.

[Fluff13]

You have repeated some of the ranges on the Details sheet, eg AN2 & AQ2 both have ='Input Page'!A15