Formula on multiple values help

Morning all

I need help getting a formula to work which is currently in Column M of the attached file.

Basically, in column V is the type .. So if i input "NSO" in this column it will then look at the data in columns P - R and then match the code in Column Q against the code in column A and then add the quantities in column R multiplied by the value in column W against the correct code.

I have left a formula in column M which should hopefully give a little more detail in what i'm trying to achieve.

Any help in getting this working would be much appreciated.

Thanks
Dave

The expected results in column M would be more helpful, a formula that doesn't do what you want only tells us what doesn't work.

In your sample column Q is identical to column A in terms of code and sort order. Is that the same in the real file?

Originally Posted by jason.b75

The expected results in column M would be more helpful, a formula that doesn't do what you want only tells us what doesn't work.

In your sample column Q is identical to column A in terms of code and sort order. Is that the same in the real file?

Hi Jason

Yes the real file is just the same but with a lot more rows.

The only difference apart from that is the actual data has been removed for privacy.

Thanks
Dave

And the expected results in column M?

Originally Posted by jason.b75

And the expected results in column M?

Yes please.

The best way to look at it is ...

Column P is a package and column Q contains the components within that package and then the quantity of each.

Column T - W are the details of the requests, So if someone requests x 2 NSO packages then the formula in column M would look at the package and match up the 2 codes (A & Q) and then deduct the quantity from the correct code but if they request 2 packages it would deduct twice the amount.

So the example that's already in there .. Someone has requested 1 "NSO" package .. So codes A01 - A07 should show 1 in each of those rows for column M so the live stock quantity in Column N is always correct with what is available.

Cheers
Dave

Originally Posted by jason.b75

Ok, just to confirm one point that could break the formula if it's not done correctly.

Will any codes in Q be repeated? i.e. could one component be used in 2 or more different packages?

If it could, then how would it affect the layout of the tables?

Simple terms, this should work. (If you want the results as positive numbers then you just need to enclose the formula in ABS()

=SUMPRODUCT((P2=$V$2:$V$17)*$W$2:$W$17*R2)

But duplicates could cause errors.

You can't multiply with sumifs, only add.

Hi Jason

Yes it is possible that an item might exist in more than 1 package, I did try to compensate for this by matching against the type in both tables also.

They would just be added in a new line in the "Package list" like the below example where NSO and NGO both contain A02

Type Code Qty
NSO A01 -1
NSO A02 -1
NSO A03 -1
NSO A04 -1
NSO A05 -1
NSO A06 -1
NSO A07 -1
BOPC A08 -1
BOPC A09 -1
BOPC A10 -1
BOPC A11 -1
BOPC A12 -1
NGO A16 -1
NGO C01 -1
NGO C02 -1
NGO C03 -1
NGO A02 -1

Thanks
Dave

So the new row would be in P:R but not in A:N, where NSO A02 and NGO A02 would have a cumulative total in Row 3?

I'm going to be away for the rest of the day, someone else will probably pick up here, if not I'll have another look later tonight, or in the morning.

Hi Jason

That's correct yes P-R could contain duplicate codes but the structure of A:N wont change unless new code items are added.

And no problem, cheers for your help.

Thanks
Dave

This formula is based on the 3 tables in the sample file, it looks right as long as I've understood what you need correctly.

=SUMPRODUCT(SUMIFS($R$2:$R$17,$P$2:$P$17,$V$2:$V$17,$Q$2:$Q$17,A2),$W$2:$W$17)

For your real file, I would suggest using dynamic ranges / structured tables in order to reduce the amount of calculation effort that may be required.

Thanks for the help Jason.

That formula does exactly what i need it to do.

Cheers
Dave