COUNTIFS WITH DISTINCT

Archie Villa

HI Everyone,

I am trying to calculate the distinct values in column Contain using countifs and distinct however i need the range of contain to be included in the range.

Column7,8 week3 will be my source chart.

COUNT(DISTINCT(COUNTIFS()))) i've tried using this but i can't incorporate the "contain" column, would you recommend a workaround?

thank you

https://app.smartsheet.com/b/publish?EQBCT=9a4850b961184987948525b2b6d09672

Kaveri Vipat

MicrosoftTeams-image (60).png

Hi Archie Villa, 

You can try this formula where I have used Count collects functions to find out distinct values in Contain column with respect to warehouse and week. 

=COUNT(DISTINCT(COLLECT(CONTAIN:CONTAIN, WAREHOUSE:WAREHOUSE, [Column6]2, WEEK:WEEK, [Column7]1))) 

Here, you just have to change the reference [Column6]2 and [Column7]1]  according to the WEEK and WAREHOUSE. 

 I have applied the formula in the Test sheet as well. 

https://app.smartsheet.com/b/publish?EQBCT=9a4850b961184987948525b2b6d09672&_ga=2.112388098.762379401.1689685826-1872822447.1683010570&_gl=1*gbfuhz*_ga*MTg3MjgyMjQ0Ny4xNjgzMDEwNTcw*_ga_ZYH7XNXMZK*MTY4OTc1MDAxMC45MS4xLjE2ODk3NTAwNzIuNTguMC4w 

Hope it will work for you. 

Itai Perez

Hey @Archie Villa,

You need to count the distinct values in column CONTAIN for each week? (Week 1,2,3)?

Is that what you are trying to do?

Archie Villa

Yes, i need to count the distinct values of column contain per week per whse.

Thanks

Itai Perez

Hey @Archie Villa ,

Thank you for clarifying.

Try using COUNTIFS like this:

=COUNTIFS(Week:Week, [Column6]$1, Wharehouse:Wharehouse, $[Column9]@row)

Let me know if it works for you.

Archie Villa

thanks Itai, but it did not include the distinct values in the "contain" column.

Kaveri Vipat

MicrosoftTeams-image (60).png

Hi Archie Villa, 

You can try this formula where I have used Count collects functions to find out distinct values in Contain column with respect to warehouse and week. 

=COUNT(DISTINCT(COLLECT(CONTAIN:CONTAIN, WAREHOUSE:WAREHOUSE, [Column6]2, WEEK:WEEK, [Column7]1))) 

Here, you just have to change the reference [Column6]2 and [Column7]1]  according to the WEEK and WAREHOUSE. 

 I have applied the formula in the Test sheet as well. 

https://app.smartsheet.com/b/publish?EQBCT=9a4850b961184987948525b2b6d09672&_ga=2.112388098.762379401.1689685826-1872822447.1683010570&_gl=1*gbfuhz*_ga*MTg3MjgyMjQ0Ny4xNjgzMDEwNTcw*_ga_ZYH7XNXMZK*MTY4OTc1MDAxMC45MS4xLjE2ODk3NTAwNzIuNTguMC4w 

Hope it will work for you. 

Archie Villa

Thank you! @Kaveri Vipat and @Itai appreciate your support on this.

Best regards,

Archie