# 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.

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.

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.