Solved

formula to return the unique entry in filtered list or "All" if a combo exists

Posted on 2014-04-29
2
143 Views
Last Modified: 2014-04-29
I need a formula that returns from text in column B "All" if visible rows under "Team" in col B contain more than 1 unique text value, or returns "1" if all visible rows contain only "1" or returns "2" when all visible rows only contain "2" ... all this as a result of having AutoFiltered on "Team", col B.  A sample file is attached.
Berry
count-visible-TorF.xlsx
0
Comment
Question by:Berry Metzger
2 Comments
 
LVL 21

Accepted Solution

by:
Ejgil Hedegaard earned 500 total points
ID: 40030842
This can do it

=IF(SUBTOTAL(9,$B$5:$B$206)=COUNTIF($B$5:$B$206,1),1,IF(SUBTOTAL(9,$B$5:$B$206)=COUNTIF($B$5:$B$206,2)*2,2,"All"))

Open in new window


Some of the values in column B was text, and some numbers.
I have converted the text to numbers.
count-visible-TorF.xlsx
0
 

Author Closing Comment

by:Berry Metzger
ID: 40031068
Your formula is what I need.  Thanks for your effort.
Berry
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now