COUNTIFS WITH DISTINCT

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
Best Answer
-
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.
Hope it will work for you.
Thanks,
Kaveri Vipat
Senior Associate - Smartsheet Development, Ignatiuz Software
2023 Core Product Certified
Did this answer help you? Show some love by marking this answer as "Insightful💡" or "Awesome❤️" and "Vote Up⬆️"
Answers
-
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?
Itai Perez
Project Manager | Transformation Department
Gong cha
If you found my comment helpful any reaction, Insightful, Awsome etc... would be appreciated🙂
-
Yes, i need to count the distinct values of column contain per week per whse.
Thanks
-
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.
Itai Perez
Project Manager | Transformation Department
Gong cha
If you found my comment helpful any reaction, Insightful, Awsome etc... would be appreciated🙂
-
thanks Itai, but it did not include the distinct values in the "contain" column.
-
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.
Hope it will work for you.
Thanks,
Kaveri Vipat
Senior Associate - Smartsheet Development, Ignatiuz Software
2023 Core Product Certified
Did this answer help you? Show some love by marking this answer as "Insightful💡" or "Awesome❤️" and "Vote Up⬆️"
-
Help Article Resources
Categories
Check out the Formula Handbook template!