Count with condition and not include duplicate

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

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!

Categories:

Building Power Apps

All responses (4)

Answers (1)

Sort by

PavanKumar 479 on at

Like
a
(1)

Report
Copy link

Link copied!

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
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

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.
Verified answer

PavanKumar 479 on at

Like
a
(1)

Report
Copy link

Link copied!

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
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Thanks! It worked!
I used
CountRows(Distinct(Filter(Table;Conditions1...));Name))