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:

Column 1	Column 2	Column 3	Column 4	Column 5
Company Name	Date	Director Name	Director Designation	No. of Directors
20 Microns Ltd.	3/31/2002	Chandresh Parikh	Chairperson & Managing Director	?
		Bhupendra B Parikh	Vice Chairperson
		Bhanu B Patel	Chairperson Emeritus
		Dhimant B Parikh	Executive Director
		Rajesh Parikh	Executive Director
		Sudhir R Parikh	Executive Director
		Darshana Mankad (Mrs.)	Director
		Sanatkumar P Dave	Director
		Vishnu Varshney	Director (GVFL)
		Dhruva S Chaudhri	Director (GVFL)
		Lalita D Sharma (Mrs.)	Director (IDBI)
		Vitthaldas D Talati	Director & Co. Secretary
A G C Networks Ltd.	3/31/2002	K A Chaukar	Chairperson	?
		Niru Mehta	Vice Chairperson
		Horatio Wong	Director
		S Chandrasekhar	Director
		S Ramakrishan	Director
		N Srinath	Director
		Pradeep Mallick	Director
		C B Bhave	Director
A M L Steel Ltd.	3/31/2002	R R Agarwal	Chairperson	?
		Poonam Chand Jangir	Director
		Ajay Agarwal	Director
A V T Natural Products Ltd.	3/31/2002	Ajit Thomas	Chairperson	?
		M S A Kumar	Executive Director
		M A Alagappan	Director
		Shyam B Ghia	Director
		Habib Hussain	Director

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...