none
Sum.if a value get reached, labeled with the date of delivery

    질문

  • Hi,

    Within our company we have a planning which tells the needs of our products with the date of our delivery to our customer. (as shown in the table)


    Article  Need units Delivery
    8 16.100 pc. 22-5-2018
    20 22.500 pc. 22-5-2018
    15 22.500 pc. 22-5-2018
    64 75 pc. 22-5-2018
    54 1.818 pc. 22-5-2018
    25 4.244 pc. 29-5-2018
    25 3.000 pc. 28-5-2018
    4 1.683 pc. 28-5-2018
    25 1.683

    pc. 

    28-5-2018

    I would like to know how i can make a calculation with shows:

    A sum of the the needs to a certain value (for example a minimum of 7000 pieces for product '25') which also shows the date that's in the same row (Can be a different calculation, doesn't really matter).

    Something like: A7 = True at 28-5-2018

    I would like to hear your solutions,

    Kind regards..


    2018년 5월 28일 월요일 오후 2:33

모든 응답

  • Hi Cornelis,

    If I understand correctly, you may try the following array formula.

    Let's say you need to sum the needs for product 25 to 8, type the formula below in the destination cell, and then press Ctrl+Shift+Enter to make it an array formula:

    ="A"&MATCH(1=1,SUMIF(OFFSET(A$1,,,ROW(1:10)),"25",B$1)>=8,)&"=True"&" at "&OFFSET($D$1,MATCH(1=1,SUMIF(OFFSET(A$1,,,ROW(1:10)),"25",B$1)>=8,)-1,0)

    Note: You may need to change the data range("1:10") and the minimum value(8) according to your situation.

    Hope this can be helpful.

    Regards,

    Yuki Sun


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.


    2018년 5월 29일 화요일 오전 3:28
    중재자
  • Hi Cornelis,

    I am writing to see if you have checked my formula above. If my reply was helpful, it would be appreciated that you can help mark it as answer so that others who might have a similar issue can benefit from your thread. If you need further explanation on the formula, please feel free to post back.

    Regards,

    Yuki Sun


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    2018년 5월 30일 수요일 오전 9:51
    중재자