cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Trung
Level: Powered On

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

Thanks in advanced!

1 ACCEPTED SOLUTION

Accepted Solutions
PavanKumar
Level 8

Re: Count with condition and not include duplicate

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.

 

Mark answer as verified if it answers your question.

Regards,

Pavan Kumar Garlapati

View solution in original post

4 REPLIES 4
PavanKumar
Level 8

Re: Count with condition and not include duplicate

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

Trung
Level: Powered On

Re: Count with condition and not include duplicate

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.

PavanKumar
Level 8

Re: Count with condition and not include duplicate

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.

 

Mark answer as verified if it answers your question.

Regards,

Pavan Kumar Garlapati

View solution in original post

Trung
Level: Powered On

Re: Count with condition and not include duplicate

Thanks! It worked!

I used 

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

Helpful resources

Announcements
New Ranks and Rank Icons in April

'New Ranks and Rank Icons in April

Read the announcement for more information!

Better Together’ Contest Finalists Announced!

'Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

thirdimage

Community Summit North America

Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (8,257)