Count Unique values with a condition

excel excel-formula formula pivot-table array-formulas
Tejas picture Tejas · Apr 11, 2013 · Viewed 70.7k times · Source

In column A I have list of different names. In column B, I have values either 0 or 1.

I want to get a count of all the unique names from column A which have 1 in column B.

Using below array formula I am able count unique names but not able to apply condition on column B.

=SUM(1/COUNTIF(A:A,A:A))

Answer

barry houdini picture barry houdini · Apr 11, 2013

Assuming no more than 100 rows try this "array formula" to count the different names in A2:A100 where there is a 1 in the same row in B2:B100:

=SUM(IF(FREQUENCY(IF(B2:B100=1,IF(A2:A100<>"",MATCH(A2:A100,A2:A100,0))),ROW(A2:A100)-ROW(A2)+1),1))

confirmed with CTRL+SHIFT+ENTER

Note that I say different not unique as the two are not the same

Related questions

Simulate string split function in Excel formula

I am trying to split a string in an excel formula, something like I can do in many programming languages, e.g. string words = "some text".split(' '); The problem is that I can't be sure that there is …

Read More
Retrieving Values From Excel Merged Columns

I'm doing a hlookup against a value that spans multiple columns. My data is similar to this: A B C D --------------------------- 1| Col1 Col2 2| x y z w 3| 4| In rows 3 and 4 (A3, B3, C3, D3, etc.), I'd like to put …

Read More
Returning cell values from other sheets by referencing sheet names from a list

I have a spreadsheet with approx twenty different sheets named by stock ticker. Because these are templates, the information in each sheet is found in the same cells. For example, the EPS for the current year is always in cell …

Read More