Help in Counting rows

excelhelpplz

New Member
Joined
Apr 16, 2014
Messages
8
Hello All,

I am using Excel 97, Windows XP. I need help in counting. Sample of my data is given below:
Column 1Column 2Column 3Column 4Column 5
Company NameDateDirector NameDirector DesignationNo. of Directors
20 Microns Ltd. 3/31/2002Chandresh ParikhChairperson & Managing Director?
Bhupendra B ParikhVice Chairperson
Bhanu B PatelChairperson Emeritus
Dhimant B ParikhExecutive Director
Rajesh ParikhExecutive Director
Sudhir R ParikhExecutive Director
Darshana Mankad (Mrs.)Director
Sanatkumar P DaveDirector
Vishnu VarshneyDirector (GVFL)
Dhruva S ChaudhriDirector (GVFL)
Lalita D Sharma (Mrs.)Director (IDBI)
Vitthaldas D TalatiDirector & Co. Secretary
A G C Networks Ltd. 3/31/2002K A ChaukarChairperson?
Niru MehtaVice Chairperson
Horatio WongDirector
S ChandrasekharDirector
S RamakrishanDirector
N SrinathDirector
Pradeep MallickDirector
C B BhaveDirector
A M L Steel Ltd. 3/31/2002R R AgarwalChairperson?
Poonam Chand JangirDirector
Ajay AgarwalDirector
A V T Natural Products Ltd. 3/31/2002Ajit ThomasChairperson?
M S A KumarExecutive Director
M A AlagappanDirector
Shyam B GhiaDirector
Habib HussainDirector
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
Joined
Jul 25, 2013
Messages
1,336
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
Joined
Apr 16, 2014
Messages
8
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...
 
Last edited:

DILIPandey

Well-known Member
Joined
Jul 25, 2013
Messages
1,336
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
Joined
Apr 16, 2014
Messages
8
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
Joined
Jul 25, 2013
Messages
1,336
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
 

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top