VB Code / Formula - in sheet

[Vishaal]

Hi All,

Thanks in advance.

i have the below sheet

S.No.	Number	Description
1	5
2	3
3	8	Na
4	1
5	7	Na
6	9	Na
7	6	Na
8	4
9	1
10	7	Na
11	6	Na
12	2
13	7	Na
14	8	Na
15	4
16	3	Na

<tbody>
</tbody>

Now i want the result in fourth column (It will check the numbers of "Na" from description column and show the number in "Result" column), please check the below table

S.No.	Number	Description	Result
1	5
2	3
3	8	Na	1
4	1
5	7	Na
6	9	Na
7	6	Na	3
8	4
9	1
10	7	Na
11	6	Na	2
12	2
13	7	Na
14	8	Na	2
15	4
16	3	Na	1

<tbody>
</tbody>

in "Result" column i am also checking that the how many times showing result is repeated,
i mean
One= 2 times
Two= 2 times
Three = One time

 

[Peter_SSs]

Something like this?
Each formula copied down.

Excel Workbook
	A	B	C	D	E	F
1	S.No.	Number	Description	Result	Results	Repeats
2	1	5			1	2
3	2	3			2	2
4	3	8	Na	1	3	1
5	4	1			4	0
6	5	7	Na
7	6	9	Na
8	7	6	Na	3
9	8	4
10	9	1
11	10	7	Na
12	11	6	Na	2
13	12	2
14	13	7	Na
15	14	8	Na	2
16	15	4
17	16	3	Na	1
18
Count Na

 

[Vishaal]

Thanks Peter_SSs ji

Many thanks for the quick reply.

Coloumn "F" is great, its working in my case.
Can, may i get the formula/vb code for getting the same result as shown in "D"

Same as in coloumn "D" not as in coloumn "E"

 

[MARK858]

Confused... Peter has already posted the formula for column D

 

[Vishaal]

Thanks Peter_SSs ji

I am really Sorry for my previous reply.

You are great, i don't know how you solveed this type of complex problems easily

Thanks

 

[Peter_SSs]

Thanks Peter_SSs ji

I am really Sorry for my previous reply.

You are great, i don't know how you solveed this type of complex problems easily

Thanks

No problem. You are welcome. Thanks for the clarification.

 

[Vishaal]

Hi Peter_SSs Ji,

This formula for coloumn "C"
D2 =IF(AND(C2="Na",C3=""),COUNTIF(C$2:C2,"Na")-SUM(D$1:D1),"")

Can we use it for row no. 3, if we copy and transpose all the data

If yes, may i know the change in formula

 

[Vishaal]

it is possible, pls confirm