Varying cell ranges in formulas according to number of cells with certain value
 

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

Varying cell ranges in formulas according to number of cells with certain value
 

hi,

Am Mon, 20 May 2013 05:10:29 +0100 schrieb Dranoeler:

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

select your table = Data = Sort = sort by column C = Custom List =
New List = List Entries: Active, Inactive, Deleted = Add = Add Level
= Sort by Colum A = A to Z


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2