# Help in Counting rows

#### excelhelpplz

##### New Member
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...

Last edited:

#### DILIPandey

##### Well-known Member
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

##### New Member
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:

Thanks you very much Dilip...

Last edited:

#### DILIPandey

##### Well-known Member
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

##### New Member
OK, let me try it...

#### excelhelpplz

##### New Member
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

##### Well-known Member
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

##### Well-known Member
Neat, never thought about counting with an offset. Thanks

Thanks shyy

Regards,
DILIPandey

#### excelhelpplz

##### New Member
Thanks a lot once again...

1,082,153
Messages
5,363,459
Members
400,737
Latest member
urhen22

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...