CODEWITHSUNDEEP
excel
Matteo
Matteo

Reputation: 73

How many different values appear in an Excel column?

I have a column of repeated texts (companies) mapped to a column on whether a condition was met.

How do I count how many companies met the condition? For example the picture below should yield 2 (counting 1 for "Google" + 1 for "Apple" and 0 for "Sun")

enter image description here

Upvotes: 1

Views: 346

Answers (1)

Scott Craner
Scott Craner

Reputation: 152650

Use this array formula:

=SUM(IF($B$2:$B$11="Y",1/COUNTIFS($A$2:$A$11,$A$2:$A$11,$B$2:$B$11,"Y")))

enter image description here

Being an array formula it must be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode. If done correctly then Excel will put {} around the formula.

Upvotes: 3

Related Questions