COUNTIFS with DISTINCT/COLLECT

Francis PJ P.

Hello all,

I've been searching for an hour and I can't seem to find the proper solution. Here's an example :

I need to count the number of DISTINCT Clients if Rep is John and Renewed and Refused are both unchecked. There are several rows for the same client as there are different type but here, we want to count the number of distinct clients only.

The answer to my formulare shoud be 2. John's clients C and E have not renewed nor refused the offer.

What is the proper formula PLEAAASE !

bisaacs

Hey @Francis PJ P.,

I think I figured it out:

=COUNT(DISTINCT(COLLECT(Client:Client, Client:Client, "C", Rep:Rep, "John", Renewed:Renewed, 0, Refused:Refused, 0)))

That only counts distinct rows that are Client C and the Rep is John, as well as if Renewed and Refused are unchecked. A few things to note:

1. You have to make sure the ranges in every part of the formula are the same size (both column-wide and row count), so if you use a range of rows for the Client column (say Client1:Client50), the rest of the ranges that are entered must also use rows 1-50.
2. Checkboxes are binary so use 1 and 0 as indicators if it's checked (1) or unchecked (0). These are integers, not strings.
3. I'm not 100% sure if the first range in COLLECT has to be Client:Client, but it seems to work that way so I wouldn't mess with it.

Hope this helps!

Francis PJ P.

@bisaacs Thank you for your reply!

If I may, I don't need to find how many times Client C only appears in the column, I need to find how many Clients in total does John have that have not renewed nor have refused.

bisaacs

@Francis PJ P. Ah got it, then you'd just remove the "Client:Client" ranges, and for the first two ranges in the COLLECT function you'd put Rep:Rep

DKazatsky2

Hi @Francis PJ P.,

Here is the formula using cross sheet references and looking at all clients.

=COUNT(DISTINCT(COLLECT({Client}, {Rep}, "John", {Renewed}, 0, {Refused}, 0)))

Hope this helps,

Dave

Mark.poole

Hey @Francis PJ P.

It took me a moment but I was able to create a formula. I also added an if statement to filter the rows that have checks

=IF(OR(Renewed@row = 1, Refused@row = 1), "", COUNT(DISTINCT(COLLECT(Client:Client, Rep:Rep, Rep@row, Renewed:Renewed, 0, Refused:Refused, 0))))

This Formula looks at not only Jon but any rep.

I hope this helped

Francis PJ P.

https://community.smartsheet.com/discussion/comment/434931#Comment_434931

I need the formula to be in the sheet summary, would that be the same ?

DKazatsky2

@Francis PJ P.,

In that case, for a sheet summary, use this.

=COUNT(DISTINCT(COLLECT(Client:Client, Rep:Rep, "John", Renewed:Renewed, 0, Refused:Refused, 0)))