+ Reply to Thread
Results 1 to 11 of 11

Formula on multiple values help

  1. #1
    Forum Contributor
    Join Date
    01-28-2014
    Location
    Manchester, UK
    MS-Off Ver
    Microsoft Office 365
    Posts
    127

    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
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    06-08-2012
    Location
    swindon, england
    MS-Off Ver
    Excel 2019 / Windows 10
    Posts
    4,546

    Re: Formula on multiple values help

    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?

  3. #3
    Forum Contributor
    Join Date
    01-28-2014
    Location
    Manchester, UK
    MS-Off Ver
    Microsoft Office 365
    Posts
    127

    Re: Formula on multiple values help

    Quote Originally Posted by jason.b75 View Post
    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

  4. #4
    Forum Expert
    Join Date
    06-08-2012
    Location
    swindon, england
    MS-Off Ver
    Excel 2019 / Windows 10
    Posts
    4,546

    Re: Formula on multiple values help

    And the expected results in column M?

  5. #5
    Forum Contributor
    Join Date
    01-28-2014
    Location
    Manchester, UK
    MS-Off Ver
    Microsoft Office 365
    Posts
    127

    Re: Formula on multiple values help

    Quote Originally Posted by jason.b75 View Post
    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

  6. #6
    Forum Expert
    Join Date
    06-08-2012
    Location
    swindon, england
    MS-Off Ver
    Excel 2019 / Windows 10
    Posts
    4,546

    Re: Formula on multiple values help

    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.
    Last edited by jason.b75; 01-14-2019 at 07:37 AM.

  7. #7
    Forum Contributor
    Join Date
    01-28-2014
    Location
    Manchester, UK
    MS-Off Ver
    Microsoft Office 365
    Posts
    127

    Re: Formula on multiple values help

    Quote Originally Posted by jason.b75 View Post
    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

  8. #8
    Forum Expert
    Join Date
    06-08-2012
    Location
    swindon, england
    MS-Off Ver
    Excel 2019 / Windows 10
    Posts
    4,546

    Re: Formula on multiple values help

    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.

  9. #9
    Forum Contributor
    Join Date
    01-28-2014
    Location
    Manchester, UK
    MS-Off Ver
    Microsoft Office 365
    Posts
    127

    Re: Formula on multiple values help

    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

  10. #10
    Forum Expert
    Join Date
    06-08-2012
    Location
    swindon, england
    MS-Off Ver
    Excel 2019 / Windows 10
    Posts
    4,546

    Re: Formula on multiple values help

    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.

  11. #11
    Forum Contributor
    Join Date
    01-28-2014
    Location
    Manchester, UK
    MS-Off Ver
    Microsoft Office 365
    Posts
    127

    Re: Formula on multiple values help

    Thanks for the help Jason.

    That formula does exactly what i need it to do.

    Cheers
    Dave

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1