1

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

Improve this question
3
  • 1
    Which version of Excel? If 365, you can use COUNTA, UNIQUE and FILTER Commented Jun 11, 2021 at 12:48
  • @Rory yes, I am using 365. Will you be able to shed some light on how to add a filter to exclude "enrolled" from a category? Commented Jun 11, 2021 at 12:56
  • You should use insert a Pivot Table with 'Course Category' in Rows and 'Enrollment Status' in Columns Commented Jun 11, 2021 at 14:44

3 Answers 3

Reset to default
4

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

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thank you and it worked, but there is one issue. If I changed all the status in Category B to "Enrolled", it is giving the result 1 instead of 0.
Please ignore, I got the answer.
0

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.

Improve this answer

1 Comment

If I use Countif or countifs, then it will count all the values, not the unique one..right?If G has completed 4 times for User1, then it will count as 4. want the count as 1 if User 1 has status-completed or In progress
0

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

Improve this answer

1 Comment

Good alternative however, it does not address O.P's question in opening line "I am looking for a formula that counts only unique values based on the conditions."; unless you feel formula / function is inappropriate, your proposal (regardless of whether it works ) may be entirely inappropriate for the OP/circumstances. Constructive feedback my end would be to include an alternate solution if its far easier/more efficient than what the OP is looking for BUT only if we are privy to OP's ability to implement/execute; alternatively, answer the question directly and offer this supplementary soln.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.