CODEWITHSUNDEEP
excelexcel-formulauniquecountif
DrewSmith
DrewSmith

Reputation: 35

Count unique values based on a COUNTIF

I'm stuck trying to calculate the number of unique numbers in column A if column B has a yes. The formula should output a value of 2, as only numbers 1 and 4 have a Yes next to them.

enter image description here

Edit: Title, did read SCOUNTIF amended to COUNTIF

Upvotes: 0

Views: 529

Answers (2)

JvdV
JvdV

Reputation: 75840

Having Excel O365, you could use:

=COUNT(UNIQUE(FILTER(A2:A9,B2:B9="Yes")))

With earlier versions of Excel you could use:

=SUM(--(FREQUENCY(IF(B2:B9="Yes",MATCH(A2:A9,A2:A9,0)),ROW(A2:A9))>0))

Which is an array entered formula so confirmed through a combination of the keys CtrlShiftEnter

Upvotes: 2

user2922434
user2922434

Reputation: 57

I would do this with 2 new columns :

  • 1st one for creating a basis to count, it could concatenate all entries or only the one including a "yes";
  • 2nd using countif formula to find duplicates. Only rows resulting with a 1 are unique.

Two formula used :

  1. =if(B2="yes";A2&B2;"")
  2. =countif($C$2:$C$9;C2) in the D2 cell

Upvotes: 0

Related Questions