Hi all, I've been trying all morning to figure out a complex problem, and I was hoping the good people here would be able to help.

So I have a spreadsheet with a list of companies, list of users within those companies, and the status of those users(Active/Inactive/Deleted). I'm trying to determine the company-level status based on how the overall status of all the users in the company.

E.g.
A B C D
Company A User 1 Active
Company A User 2 Active
Company A User 3 Inactive
Company A User 4 Deleted
Company B User 1 Inactive
Company B User 2 Active
Company B User 3 Inactive

In the above example, cells D1 to D4 would list "Active", since Active users form the bulk of the company. D4 to D6 would list "Inactive" for Company B.

I've figured out the first half of what I need to do:
=IF(COUNTIF(A1:A4,"Active")COUNTA(A1:A4)/2),"Active",IF(COUNTIF(A1:A4,"Inactive")(COUNTA(A 1:A4)/2),"Inactive","Deleted"))

My problem is in getting Excel to automatically define the cell ranges according to the cells that contain "Company A", "Company B", etc. I have over 5,000 rows on the spreadsheet so having to manually change the cell ranges for each company would take forever.

I will be EXTREMELY grateful if anyone could help!!

Also posted on http://www.excelforum.com/excel-gene...39#post3245639, and http://www.mrexcel.com/forum/excel-q...ain-value.html