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.
Thanks for your suggestion. But I want the value in a single cell. The row number of the required cell till which the count will reach the desired value.
For the conditions I have a separate table where I need the required row number against each condition (a,b,c) in single cell for each condition.
Soumyadip47 You don't mention what Excel version you are on, but perhaps the solution in the attached workbook works for you.
Thanks for your help.
But when I enter 1 as the upto counting criteria, the result is coming 7, it should come 2.
- Better edit your post & share the WB or some sample data with us ,, including all possibilities,, and let us to fix it !
- I have edited and posted my requirement in details
Soumyadip47 Sorry, didn't think straight there. Try the attached (revised) file.
