Countif till a certain value is reached

[Soumyadip47]

Hi,

I am editing my post and posting the whole requirement (attached workbook) which I am working on currently. This is basically a shortage report.

In the worksheet named "details" I have list of machines with machine serial number (MSN) sorted in ascending order of manufacturing dates with configuration details (column D to I). Also there are some special categories in which they fall as per their configuration details which are being already marked as "1" from column J to R in same worksheet.

Now in the second worksheet named "Critical components", I have the list of items which are being fitted in these machines with per set usage mentioned against special categories (as was in "details" worksheet) from Column E to M. Now my column D contains clear till date in numbers. Now in column N, I want that as per the clear till date data, it will tell me till which row number in worksheet "Details" the particular material is clear. This will be calculated considering the per set usage and the usage in special configuration category.

Regards,

Soumyadip.

[#2]

@Soumyadip47 

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.

[#3]

@Rajesh_Sinha 

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.

[#4]

@Soumyadip47 You don't mention what Excel version you are on, but perhaps the solution in the attached workbook works for you. 

[#5]

@Riny_van_Eekelen 

Thanks for your help.

But when I enter 1 as the upto counting criteria, the result is coming 7, it should come 2. 

[#6]

@Soumyadip47 Sorry, didn't think straight there. Try the attached (revised) file.

[#7]

Better edit your post & share the WB or some sample data with us ,, including all possibilities,, and let us to fix it !

[#8]

I have edited and posted my requirement in details

[#9]

@Soumyadip47 

Hope this work for U,, otherwise I'm clueless!! 

[#10]

Thanks for your help.
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.

[#11 · Accepted solution]

@Soumyadip47 

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.

[#12]

@JMB17
Thanks a lot for your response. This is perfect and it is the solution which I was asking.


Regards,
Soumyadip.

[#13]

You're quite welcome.

[#14]

If possible can you please explain the formula you have used, so that I can learn from it.

[#15]

@Soumyadip47 

I added some additional tabs and some explanation to break it down into individual steps. 

[#16]

Thanks a lot