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.
I would like to suggest two possible methods to you,, where method 1 needs Helper data, and other has composite formula.
How it works:
- Formula in cell V2:
=COUNTIF($U$2:$U2,U2)- Formula in cell W2:
=IF((V2>=2)*(V2<=4)*(U2="a"),2,V2)- Formula in cell X2:
=IF(AND(COUNTIF($U$2:$U2,U2)>=2,COUNTIF($U$2:$U2,U2)<=4),2,COUNTIF($U$2:$U2,U2))
N.B.
- You may adjust cell references & True/False value as needed.
** Please confirm whether this works or not,, and if works then you may accept it as an best Answer as well Like too.
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 !
