CODEWITHSUNDEEP
excelexcel-formula
Cath
Cath

Reputation: 125

Count unique values

I am looking for a formula that counts only unique values based on the conditions.

Scenario: As shown in the image, I have three different enrollment statuses "Enrolled", "Completed", and "In Progress". What I am trying to achieve:

Check if value of Column A values match/contain with Column F. If it does, then look at Column G. If a user has the status "Completed" or "Inprogress", then count those users as 1. If the user status is "Enrolled", then do not count those users.

In B2 I should get the count 2, as "[email protected]" and "[email protected]" have the status "Completed" or "In Progress", whereas "[email protected]" doesn't have.

Can anyone please help me with this?

enter image description here

Upvotes: 1

Views: 153

Answers (3)

will
will

Reputation: 101

PivotTables are ideal for situations like this.

Select your data set and click the "PivotTable" button on the "Insert" tab on the ribbon.

In the PivotTable Fields pane:

  1. Add Enrollment Status to the Filters and filter it to show only Enrollment Status equal to In Progress or Completed.
  2. Add the Username to the Values field and set it to summarize values by Count. (it will probably default to a count)
  3. Add the Course Category to the Rows.

This will return a table with exactly what you're looking for.

Screenshot

Upvotes: 0

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19712

You're interested in counting the emails - so first is to filter the list to the relevant emails.

Replace the literals with your cell references - Category A to A2 for example.
=FILTER(D2:D15,(F2:F15="Category A")*($G$2:$G$15<>"Enrolled"))

Now to get the uniques:
=UNIQUE(FILTER(D2:D15,(F2:F15="Category A")*($G$2:$G$15<>"Enrolled")))

and finally count the values:
=COUNTA(UNIQUE(FILTER(D2:D15,(F2:F15="Category A")*($G$2:$G$15<>"Enrolled"))))

Upvotes: 5

Capt.Krusty
Capt.Krusty

Reputation: 627

You can try with the COUNTIF or COUNTIFS functions (depending how many criteria you may give). Formula in Cell B2 could be:

=COUNTIFS($F$2:$F$10;$A$2;$G$2:$G$10;"In Progress")+COUNTIFS($F$2:$F$10;$A$2;$G$2:$G$10;"Completed")

It neglects the "Enrolled" since you do not want to count them.

Upvotes: 0

Related Questions