Solved

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

Posted on 2014-04-29
143 Views
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
Question by:Berry Metzger

LVL 21

Accepted Solution

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"))
``````

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

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

## Join & Write a Comment Already a member? Login.

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.

#### Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!