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]

You are welcome....

Cheers


Regards,
DILIPandey

 

[Peter_SSs]

I am using Excel 97,

1. I assume that is a typo, else you wouldn't find the IFERROR() function working very well for you.

2. With 14 sheets of 16,000 rows I think you will find a considerable performance degradation using the volatile function OFFSET and that many array formulas.

This should use considerably less resources.
Note that the range in the SUM() part of the formula extends 1 row below the bottom of the data.
Also, I have hidden 2 columns to keep my screen shot a bit smaller.

Excel Workbook
	A	D	E
1	Company Name	Director Designation	No. of Directors
2	20 Microns Ltd.	Chairperson & Managing Director	10
3		Vice Chairperson
4		Chairperson Emeritus
5		Executive Director
6		Executive Director
7		Executive Director
8		Director
9		Director
10		Director (GVFL)
11		Director (GVFL)
12		Director (IDBI)
13		Director & Co. Secretary
14	A G C Networks Ltd.	Chairperson	6
15		Vice Chairperson
16		Director
17		Director
18		Director
19		Director
20		Director
21		Director
22	A M L Steel Ltd.	Chairperson	2
23		Director
24		Director
25	A V T Natural Products Ltd.	Chairperson	4
26		Executive Director
27		Director
28		Director
29		Director
30
Count

 

[excelhelpplz]

1. I assume that is a typo, else you wouldn't find the IFERROR() function working very well for you.

No doubt, this forum is one of the best forum in the world and you have proved that you are the right person for moderator post.

Really awesome and great help from you....

Thanks in tons....

 

[Peter_SSs]

Glad to have helped.

 

[DILIPandey]

No doubt, this forum is one of the best forum in the world and you have proved that you are the right person for moderator post.

Correct excelhelpplz, I agree with you. Thanks to Peter.


Regards,
DILIPandey

 

[excelhelpplz]

Hello Friends,

Now, I have another excel files (given below). I have to count number of independent directors given in column D. I used old function provided by you with small modification. I have replaced "director" with "Independent". However, with this change function is not working as it counting "Non-Independent" also.
[TABLE="width: 664"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Date[/TD]
[TD]Director Name[/TD]
[TD]Non-independent/Independent[/TD]
[/TR]
[TR]
[TD]20 Microns Ltd.[/TD]
[TD] 3/31/2002[/TD]
[TD]Chandresh Parikh[/TD]
[TD]Non-independent[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Bhupendra B Parikh[/TD]
[TD]Non-independent[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Bhanu B Patel[/TD]
[TD]Non-independent[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Dhimant B Parikh[/TD]
[TD]Non-independent[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Rajesh Parikh[/TD]
[TD]Non-independent[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Sudhir R Parikh[/TD]
[TD]Non-independent[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Darshana Mankad (Mrs.)[/TD]
[TD]Independent[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Sanatkumar P Dave[/TD]
[TD]Independent[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Vishnu Varshney[/TD]
[TD]Independent[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Dhruva S Chaudhri[/TD]
[TD]Independent[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Lalita D Sharma (Mrs.)[/TD]
[TD]Independent[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Vitthaldas D Talati[/TD]
[TD]Independent[/TD]
[/TR]
[TR]
[TD]3M India Ltd.[/TD]
[TD] 12/31/2002[/TD]
[TD]Yashovardhan Birla[/TD]
[TD]Non-independent[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Lee M Kennedy[/TD]
[TD]Non-independent[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]William G Allen[/TD]
[TD]Non-independent[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Paul Rosso (Dr.)[/TD]
[TD]Non-independent[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Mary B Mccormick (Ms.)[/TD]
[TD]Non-independent[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Thomas P Spencer[/TD]
[TD]Non-independent[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]B S Iyer[/TD]
[TD]Independent[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Amit Mukherjee[/TD]
[TD]Independent[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]D J Balaji Rao[/TD]
[TD]Independent[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Brad C Sweet[/TD]
[TD]Non-independent[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]B V Shankaranarayana Rao[/TD]
[TD]Non-independent[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Carlos A Torres[/TD]
[TD]Non-independent[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Mark C Sinnard[/TD]
[TD]Non-independent[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Robert K Monette[/TD]
[TD]Non-independent[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]G Shanker Prasad[/TD]
[TD]Non-independent[/TD]
[/TR]
[TR]
[TD]A C C Ltd.[/TD]
[TD] 3/31/2002[/TD]
[TD]Tarun Das[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]N S Sekhsaria[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]T M M Nambiar[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]N A Soonawala[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]O P Dubey[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]A L Kapur[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]S M Palia[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Cyril S Shroff[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]M L Narula[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]P K Sinor[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]A K Jain[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A G C Networks Ltd.[/TD]
[TD] 3/31/2002[/TD]
[TD]K A Chaukar[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Niru Mehta[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Horatio Wong[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]S Chandrasekhar[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]S Ramakrishan[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]N Srinath[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Pradeep Mallick[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]C B Bhave[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Vishal Kohli[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A M L Steel Ltd.[/TD]
[TD] 3/31/2002[/TD]
[TD]R R Agarwal[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Poonam Chand Jangir[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Ajay Agarwal[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]P Venkatesan[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A V T Natural Products Ltd.[/TD]
[TD] 3/31/2002[/TD]
[TD]Ajit Thomas[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]M S A Kumar[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]M A Alagappan[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Shyam B Ghia[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Habib Hussain[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]V Suresh[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Abbott India Ltd.[/TD]
[TD] 11/30/2002[/TD]
[TD]Munir Shaikh[/TD]
[TD]Non-independent[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]D M Gavaskar[/TD]
[TD]Non-independent[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]R A Shah[/TD]
[TD]Independent[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]V D Narkar[/TD]
[TD]Independent[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Thomas Chen[/TD]
[TD]Non-independent[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]David Wardell[/TD]
[TD]Non-independent[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]G S Kurmi[/TD]
[TD]Non-independent[/TD]
[/TR]
</tbody>[/TABLE]
Thanks a lot for your help....

 

[Peter_SSs]

Now, I have another excel files (given below). I have to count number of independent directors given in column D. I used old function provided by you with small modification. I have replaced "director" with "Independent". However, with this change function is not working as it counting "Non-Independent" also.

That is correct because the two * characters surrounding the word means to count anything that contains the word at all. So, just remove the * characters and see how it goes. It should then just count cells that have the exact word "Independent"

 

[excelhelpplz]

Yes, it is working now...
Thanks in tons..