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

[Dranoeler]

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 withi
those companies, and the status of those users(Active/Inactive/Deleted)
I'm trying to determine the company-level status based on how th
overall status of all the users in the company

E.g.
A B C
Company A User 1 Active
Company A User 2 Activ
Company A User 3 Inactiv
Company A User 4 Deleted
Company B User 1 Inactiv
Company B User 2 Activ
Company B User 3 Inactiv

In the above example, cells D1 to D4 would list "Active", since Activ
users form the bulk of the company. D4 to D6 would list "Inactive" fo
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(A1:A4)/2),"Inactive","Deleted")

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

I will be EXTREMELY grateful if anyone could help!

Also posted on http://tinyurl.com/mr73ap2, an
http://tinyurl.com/mtpwe6

 

[Claus Busch]

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