Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 Last edited by Dranoeler : May 20th 13 at 05:43 AM |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert a fixed number of lines between a varying range of cells | Excel Programming | |||
Varying selected cells returning value always to the same cell | Excel Discussion (Misc queries) | |||
Include varying cell references in formulas based on changing crit | Excel Worksheet Functions | |||
Calculation for varying ranges | Excel Worksheet Functions | |||
Copy cells with varying ranges (VBA) | Excel Programming |