Forum Discussion
Countif till a certain value is reached
I'm not sure my understanding is correct, but try this in N3:
=MAX((MMULT(--(ROW(Details!$J$2:$J$172)>=TRANSPOSE(ROW(Details!$J$2:$J$172))),MMULT(E3:M3*Details!$J$2:$R$172,--(TRANSPOSE(COLUMN(E3:M3)>0))))<=D3)*ROW(Details!$J$2:$J$172))You may need to hit Ctrl+Shift+Enter, unless you have the newest version of excel with dynamic arrays that may not require it.
The formula does rely on the categories in Details columns J-R and Critical Components Columns E-M being in the same order.
Also, I'm assuming you will be adding rows to the details tab? If so, I would add an empty border row at the bottom and insert new rows there (at row 172) - if you insert a row there, then you won't have to change the formula to reference your new rows (excel will take care of it).
See attached workbook.
But I think there is some misunderstanding. The column D (named clear till date (in nos)) in worksheet named "Critical component" denotes the number of that component available with me.
For the data which I have shared, since we have 10 numbers of material AAAAAA. Now AAAAAA is used in the category EX200/210/215 (AC) & EX200/210/215 (RB) with 1 qty/set usage. Then as per the usage, this will complete my production till row number 23 as till that row the number, the number of machines (count) with the specific category EX200/210/215 (AC) & EX200/210/215 (RB) is 10.
I'm not sure my understanding is correct, but try this in N3:
=MAX((MMULT(--(ROW(Details!$J$2:$J$172)>=TRANSPOSE(ROW(Details!$J$2:$J$172))),MMULT(E3:M3*Details!$J$2:$R$172,--(TRANSPOSE(COLUMN(E3:M3)>0))))<=D3)*ROW(Details!$J$2:$J$172))
You may need to hit Ctrl+Shift+Enter, unless you have the newest version of excel with dynamic arrays that may not require it.
The formula does rely on the categories in Details columns J-R and Critical Components Columns E-M being in the same order.
Also, I'm assuming you will be adding rows to the details tab? If so, I would add an empty border row at the bottom and insert new rows there (at row 172) - if you insert a row there, then you won't have to change the formula to reference your new rows (excel will take care of it).
See attached workbook.
- Thanks a lot
I added some additional tabs and some explanation to break it down into individual steps.
- If possible can you please explain the formula you have used, so that I can learn from it.
- You're quite welcome.
- JMB17
Thanks a lot for your response. This is perfect and it is the solution which I was asking.
Regards,
Soumyadip.