cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Count with condition and not include duplicate

Hi guys!

How can we Count with some conditions but exclude doublicates?

For example: a Table like this:

No.   Name   Status

1       AAA      YES

2       BBB       NO

3       CCC      YES

4       AAA      NO

5       AAA      YES

6       AAA      NO

7       AAA      YES

8       DDD      NO

How can I have a formula to:

return 2 with status = YES (for AAA and CCC);

return 3 with status = NO (for BBB, AAA, DDD);

1 ACCEPTED SOLUTION

Accepted Solutions
Responsive Resident

Hi,

You don't have to push the list to collection, You can directly use the collection in the formulae and add as many conditions as you would like.

Regards,

Pavan Kumar Garlapati

4 REPLIES 4
Responsive Resident

Hi Trung,

You can use below formulae to get values avoiding duplicates. Lets say your actual list (which you have mentioned below) is in List Status Collection.

ClearCollect(StatusCollection,
{ Name: "AAA", Status: "YES"},
{ Name: "BBB", Status: "NO"},
{ Name: "CCC", Status: "YES"},
{ Name: "AAA", Status: "NO"},
{ Name: "AAA", Status: "YES"},
{ Name: "AAA", Status: "NO"},
{ Name: "AAA", Status: "YES"},
{ Name: "DDD", Status: "NO"}
);

//List which gets Unique Yes Names

ClearCollect(DistinctYesStatus, Distinct(Filter(StatusCollection, Status ="YES"),Name));

//List which gets Unique No Names.

ClearCollect(DistinctNOStatus, Distinct(Filter(StatusCollection, Status ="NO"),Name));

Mark it as verified if it answers your problem.

Regards,

Pavan Kumar Garlapati

Anonymous
Not applicable

Thank @PavanKumar;

My list is in Excel and the conditions are complicated (many conditions).

I am looking for a solution without push the list to a Collection.

Responsive Resident

Hi,

You don't have to push the list to collection, You can directly use the collection in the formulae and add as many conditions as you would like.

Regards,

Pavan Kumar Garlapati

Anonymous
Not applicable

Thanks! It worked!

I used

`CountRows(Distinct(Filter(Table;Conditions1...));Name))`

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

#### Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (2,413)