Defining cell ranges in formulas according to cells with a 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.

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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, it would save me a TON of work!!

Also posted on http://www.mrexcel.com/forum/excel-q...ain-value.html, and http://www.excelbanter.com/showthread.php?t=448789

Just clarify us about the method based on how you are marking D1:D4 as Active and D5:D7 as Inactive?

We will offer you the formula, but we need to know the logic about how you are marking those entries as Active/Inactive?

Hi and welcome to the forum

Based on your sample, how about something like this?
=IF(A2=A1,"",IF(COUNTIFS(A:A,A2,C:C,"Active")>=COUNTIFS(A:A,A2,C:C,"Inactive")+COUNTIFS(A:A,A2,C:C,"deleted"),"Active","Inactive"))

If you want them all to show...
=IF(COUNTIFS(A:A,A2,C:C,"Active")>=COUNTIFS(A:A,A2,C:C,"Inactive")+COUNTIFS(A:A,A2,C:C,"deleted"),"Active","Inactive")

Please don't cross post your question without supplying the link

Cross Posted @
http://www.mrexcel.com/forum/excel-q...ain-value.html

Thanks Six

I'm afraid your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

Thanks for that

Did you look at post #6?

FDibbins,

I should have been clearer on one point: I'm trying to make it where the overall company status is either Active/Inactive/Deleted, depending on the majority status of the user within the company

EDIT
MANY thanks - I've fixed the problem where "Inactive" was the only Else value by using this:

Please Login or Register  to view this content.

I'm still running into the roadblock where there are just 2 users within a company each with a different status, though. Is there something I can tack on to the above to make it return a "TIE" value in such cases?

what result do you want if there is a tie? which 1 of the 2 takes precedence?