Find and count only numbers are finding, before the exact match number

[Kishan]

Using Excel 2000

Hi,

I want to count only the numbers (not the empty cells) are finding before the exact match number in the each row for example...

In the column "A" I have list of exact match numbers, in the C3:V20 list of numbers I am looking a formula which can look number are in the column "A" and count in the row numbers are found before it.

Example1 A3 has number 7 I want this number to be looked in the range C3:V3 and count the numbers before the 7 which counts are 6, formula result in cell X3=6

Example3 A4 has number 14 I want this number to be looked in the range C4:V4 and count the numbers before the 14 which counts are 11, formula result in cell X4=11

Numbers		C1	C2	C3	C4	C5	C6	C7	C8	C9	C10	C11	C12	C13	C14	C15	C16	C17	C18	C19	C20		Result
7		1	2	3	4	5	6	7	8	9	10	11	12	13	14	15		17	18	19	20		6
14		1	2	3	4	5	6		8	9	10	11	12		14	15		17	18	19	20		11
4		1	2	3	4	5	6		8	9	10	11	12			15		17			20		3
15		1	2	3		5	6		8	9	10	11	12			15		17			20		10
33		1	2	3		5	6		8	9	10	11	12					17			20
3		1	2	3		5	6		8	9	10	11	12					17			20		2
4		1	2			5	6		8	9		11	12					17			20
1		1	2			5	6		8	9		11						17			20		0
14			2			5	6		8	9		11						17			20

Thank you in advance

Regards,
Kishan

 

[kweaver]

That's very confusing. Do you mean CELL "C3" or the CELL labeled with a "C3" ?

For the 14, starting in the column LABELED C2, there are then 10 and not 11 as you show above.

Book6
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1
2	Numbers		C1	C2	C3	C4	C5	C6	C7	C8	C9	C10	C11	C12	C13	C14	C15	C16	C17	C18	C19	C20		Result
3	7		1	2	3	4	5	6	7	8	9	10	11	12	13	14	15		17	18	19	20		6
4	14		1	2	3	4	5	6		8	9	10	11	12		14	15		17	18	19	20		10
5	4		1	2	3	4	5	6		8	9	10	11	12			15		17			20		1
6	15		1	2	3		5	6		8	9	10	11	12			15		17			20		7
7	33		1	2	3		5	6		8	9	10	11	12					17			20		9
8	3		1	2	3		5	6		8	9	10	11	12					17			20		0
9	4		1	2			5	6		8	9		11	12					17			20		0
10	1		1	2			5	6		8	9		11						17			20		0
11	14			2			5	6		8	9		11						17			20		2
Sheet17

Cell Formulas
Range	Formula
X3:X11	X3	=COUNTIF(OFFSET(C3,0,ROW()-3):V3,"<"&A3)

 

[mrshl9898]

Not suitable as per above

 

[Kishan]

That's very confusing. Do you mean CELL "C3" or the CELL labeled with a "C3" ?

For the 14, starting in the column LABELED C2, there are then 10 and not 11 as you show above.

Book6
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1
2	Numbers		C1	C2	C3	C4	C5	C6	C7	C8	C9	C10	C11	C12	C13	C14	C15	C16	C17	C18	C19	C20		Result
3	7		1	2	3	4	5	6	7	8	9	10	11	12	13	14	15		17	18	19	20		6
4	14		1	2	3	4	5	6		8	9	10	11	12		14	15		17	18	19	20		10
5	4		1	2	3	4	5	6		8	9	10	11	12			15		17			20		1
6	15		1	2	3		5	6		8	9	10	11	12			15		17			20		7
7	33		1	2	3		5	6		8	9	10	11	12					17			20		9
8	3		1	2	3		5	6		8	9	10	11	12					17			20		0
9	4		1	2			5	6		8	9		11	12					17			20		0
10	1		1	2			5	6		8	9		11						17			20		0
11	14			2			5	6		8	9		11						17			20		2
Sheet17

Cell Formulas
Range	Formula
X3:X11	X3	=COUNTIF(OFFSET(C3,0,ROW()-3):V3,"<"&A3)

Hi kweaver, you got my idea it is exact layout, thank you for the formula, I am not getting the result as per my #post1.

Please can you check it? If you check row 4 there are 11 numbers before 14 so X4 result must be 11, in X5 must be 3 and so on...

kind Regards,
Kishan

 

[kweaver]

11 numbers before 14 would start the count in column C.
Then, by that logic, there are 3 numbers before 4 in the next row and you show the result should be 1.

 

[Rick Rothstein]

Give this formula a try...

=IFERROR(COUNTIF(C3:INDEX(C3:V3,1,MATCH(A3,C3:V3,0)),"<>")-1,"")

 

[Kishan]

11 numbers before 14 would start the count in column C.
Then, by that logic, there are 3 numbers before 4 in the next row and you show the result should be 1.

Hi kweaver, if you see the image attached it shows clearly, if start counting from column C till the target number found yes before the 14 in the row4 there are 11 numbers and in the row5 there are 3 numbers before the 4 it is shown correctly but I think having a problem not showing the column and row description has created the confusion.

I appreciate your help

Kind Regards,
Kishan

 

[Kishan]

Give this formula a try...

=IFERROR(COUNTIF(C3:INDEX(C3:V3,1,MATCH(A3,C3:V3,0)),"<>")-1,"")

Hi Rick Rothstein, after inserting formula I got error #¿NAME? Searching in the excel forums I found that the "IFERROR" function is not compatible with excel 2000, as the formula was perfect and worked well without trapping the error like this

=CONTAR.SI(C3:INDICE(C3:V3;1;COINCIDIR(A3;C3:V3;0));"<>")-1

But now it were giving different error #N/A, searching again in the excel forums find the solution adapting it within your formula it has worked fine.

=IF(ISERROR(COUNTIF(C3:INDEX(C3:V3,1,MATCH(A3,C3:V3,0)),"<>")),"",COUNTIF(C3:INDEX(C3:V3,1,MATCH(A3,C3:V3,0)),"<>")-1)

I appreciate your kind help for solving my request.

Kind Regards,
Kishan

 

[kweaver]

Does this summarize it?

Book6
	X
3	6
4	11
5	3
6	10
7	0
8	2
9	0
10	0
11	0
Sheet17

Cell Formulas
Range	Formula
X3:X11	X3	=IF(0=COUNTIF(C3:V3,A3),0,COUNTIF(C3:V3,"<"&A3))

 

[Kishan]

Does this summarize it?

Book6
	X
3	6
4	11
5	3
6	10
7	0
8	2
9	0
10	0
11	0
Sheet17

Cell Formulas
Range	Formula
X3:X11	X3	=IF(0=COUNTIF(C3:V3,A3),0,COUNTIF(C3:V3,"<"&A3))

Hi kweaver, Yes indeed this is a perfect!! Also it is a simple and the finest formula solution. I like it very much. I will replace and will go with your solution.

I do appreciate your help and time you spent to solve it in one go.

Kind Regards,
Kishan