COUNTIFS - distinct values only

KateAnt

Hi, I would like to count the specific survey names with status "submitted" Each supplier should be counted once, even if survey has been submitted couple of times by different people.

In the Summary sheet, I was trying this one: =COUNTIFS([Survey Name]:[Survey Name], ="Supply Chain Visibility", Status:Status, ="Submitted") but it does not take into account the duplicate submissions.

The true result should be 6.

Any help very much appreciated!

Kate

Dakota Haeffner

Just an idea, you could have a hidden column as a helper that Counts the amount of times a supplier has submitted. Lets title that column Supplier Name Counter, that column formula would look like this..

=COUNTIF([Supplier Name]:[Supplier Name], [Supplier Name]@row)

Then to get your actual submitted surveys number you could do..

=COUNTIFS([Survey Name]:[Survey Name], "Supply Chain Visibility", Status:Status, ="Submitted", [Supplier Name Counter]:[Supplier Name Counter], =1)

I had a similar problem I needed to look at like 400 Toll Free Numbers and see duplicates and the counter helped with that.

Hope this helps.