Formula Assistance (COUNTM??)
Hello all,
My team and I are hoping to build a formula to do the following:
First, identify all items that belong to "Group A," then count how many items the rows that belong to group A have in "Color."
So, for example, the answer for this would be 4 (because there are 4 items in the "Color" column that belong to Group A).
We know that to count the "Color" column, we need to use COUNTM() and we have that working. However, we are not sure how to narrow that down to only the items in a specific group.
Could anyone help us with this?
Alison
Best Answer
-
Try a COUNTM/COLLECT. The COLLECT function will essentially populate the COUNTM function with only those rows from the multi-select based on the criteria you specify.
=COUNTM(COLLECT(Color:Color, Group:Group, "A")
Answers
-
I'm not sure I follow. Would there be multiple rows for Group A that you want to count all colors for?
-
Correct, it will have a number of rows, filling over 200 rows with Groups A-C, etc.. This is dummy data to be able to post in a public forum.
-
Try a COUNTM/COLLECT. The COLLECT function will essentially populate the COUNTM function with only those rows from the multi-select based on the criteria you specify.
=COUNTM(COLLECT(Color:Color, Group:Group, "A")
-
Thanks, Paul, this works great!!
-
Excellent! Happy to help! 👍️
-
I need to put criteria on a COUNTM. I tried using the COUNTM(COLLECT. I need it to only count if assigned to specific person and within the last 7 days. Below is what I am trying.
=COUNTM(COLLECT({Branch}, [Assigned to]@row,[{sold date},>=TODAY(-7)]){Sold Products})
-
@Jennifer Melin There are a couple of syntax issues with your COLLECT function. Try this...
=COUNTM(COLLECT({Sold Products}, {Branch}, @cell = [Assigned to]@row, {sold date}, @cell >= TODAY(-7)))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!