Help in Counting rows

[excelhelpplz]

Hello All,

I am using Excel 97, Windows XP. I need help in counting. Sample of my data is given below:
[TABLE="width: 831"]
[TR]
[TD]Column 1[/TD]
[TD]Column 2[/TD]
[TD]Column 3[/TD]
[TD]Column 4[/TD]
[TD]Column 5[/TD]
[/TR]
[TR]
[TD]Company Name[/TD]
[TD]Date[/TD]
[TD]Director Name[/TD]
[TD]Director Designation[/TD]
[TD]No. of Directors[/TD]
[/TR]
[TR]
[TD]20 Microns Ltd.[/TD]
[TD] 3/31/2002[/TD]
[TD]Chandresh Parikh[/TD]
[TD]Chairperson & Managing Director[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Bhupendra B Parikh[/TD]
[TD]Vice Chairperson[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Bhanu B Patel[/TD]
[TD]Chairperson Emeritus[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Dhimant B Parikh[/TD]
[TD]Executive Director[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Rajesh Parikh[/TD]
[TD]Executive Director[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Sudhir R Parikh[/TD]
[TD]Executive Director[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Darshana Mankad (Mrs.)[/TD]
[TD]Director[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Sanatkumar P Dave[/TD]
[TD]Director[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Vishnu Varshney[/TD]
[TD]Director (GVFL)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Dhruva S Chaudhri[/TD]
[TD]Director (GVFL)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Lalita D Sharma (Mrs.)[/TD]
[TD]Director (IDBI)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Vitthaldas D Talati[/TD]
[TD]Director & Co. Secretary[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A G C Networks Ltd.[/TD]
[TD] 3/31/2002[/TD]
[TD]K A Chaukar[/TD]
[TD]Chairperson[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Niru Mehta[/TD]
[TD]Vice Chairperson[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Horatio Wong[/TD]
[TD]Director[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]S Chandrasekhar[/TD]
[TD]Director[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]S Ramakrishan[/TD]
[TD]Director[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]N Srinath[/TD]
[TD]Director[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Pradeep Mallick[/TD]
[TD]Director[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]C B Bhave[/TD]
[TD]Director[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A M L Steel Ltd.[/TD]
[TD] 3/31/2002[/TD]
[TD]R R Agarwal[/TD]
[TD]Chairperson[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Poonam Chand Jangir[/TD]
[TD]Director[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Ajay Agarwal[/TD]
[TD]Director[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A V T Natural Products Ltd.[/TD]
[TD] 3/31/2002[/TD]
[TD]Ajit Thomas[/TD]
[TD]Chairperson[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]M S A Kumar[/TD]
[TD]Executive Director[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]M A Alagappan[/TD]
[TD]Director[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Shyam B Ghia[/TD]
[TD]Director[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Habib Hussain[/TD]
[TD]Director[/TD]
[TD][/TD]
[/TR]
[/TABLE]
In total i have 14 sheets. Each of the sheet have data in 16,000 rows (in the format mentioned above). I have to count number of directors for each of the company and write number in column 5 in the same row in which company name is given in column 1.

Please, help me. I tried it manually but it is taking so much of time. Then i found this website. I hope i will get good help from you as you all are expert in excel.

Thanks a lot...

 

[DILIPandey]

Okay.. use below formula in column 5:-


=IFERROR(COUNTIF(OFFSET($D2,0,0,IF(A2<>"",MIN(IF($A3:$A$29<>"",ROW($A3:$A$29),""))-1,"")-ROW(D1),1),"*Director*"),"")

Confirm the formula using key combination:- ctrl shift enter


Regards,
DILIPandey

 

[excelhelpplz]

Hello Dilip,

Thanks a lot for your help.
Your function is working but it count 1 less every time. For example it counts 6 in place of 7, it counts 8 in place of 9. Please, look in to it once.

I have changed A29 to A11065 as i have data up to 11065 rows. I do not know i am correct or not.

I am also sharing the file with you. Check out the link given below:

https://drive.google.com/file/d/0B9pp_RscZT39UzNyX2d1TEJvY2M/edit?usp=sharing

Thanks you very much Dilip...

 

[DILIPandey]

Is your data starting from row 1 ?

I used your sample, ignored column 1, 2 etc and pasted rest data.
I had entered the formula in cell e2.

I would say try again, use above references. Thanks.


Regards,
DILIPandey

 

[excelhelpplz]

OK, let me try it...

 

[excelhelpplz]

I got it. Actually, in 'column 4' all are directors irrespective of their post name (such as chairman etc are also directors). Therefore, I have made little change and remove "director" word from function. Please, have a look and let me know that is it fine or not:

=IFERROR(COUNTIF(OFFSET($D176,0,0,IF(A176<>"",MIN(IF($A177:$A$11063<>"",ROW($A177:$A$11063),""))-1,"")-ROW(D175),1),"**"),"")

I am sorry for not asking question clearly.


Thanks a lot for your help. You are truly genius..!

 

[DILIPandey]

Okay. but now how you will figure out if a person is director or not ?
I am asking this because you wanted to count only director.

Otherwise it will be just a simple formula of counting any 'job title' based on a dynamic range


Regards,
DILIPandey

 

[shyy]

Neat, never thought about counting with an offset. Thanks

 

[DILIPandey]

Thanks shyy


Regards,
DILIPandey

 

[excelhelpplz]

Thanks a lot once again...