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
-
Paul Newcome ✭✭✭✭✭✭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")
thinkspi.com
Answers
-
Paul Newcome ✭✭✭✭✭✭
I'm not sure I follow. Would there be multiple rows for Group A that you want to count all colors for?
thinkspi.com
-
Alison Employee
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.
-
Paul Newcome ✭✭✭✭✭✭
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")
thinkspi.com
-
Alison Employee
Thanks, Paul, this works great!!
-
Paul Newcome ✭✭✭✭✭✭
-
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})
-
Paul Newcome ✭✭✭✭✭✭
@Jennifer MelinThere 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)))
thinkspi.com
Help Article Resources
Categories
You have a multi select dropdown where you specify which area where improved within each improvement idea.<\/p>
If that is correct, your first formula should work, although it would be better practice to use the HAS function for this setup, see below: <\/p>
=COUNTIF({Areas Improved}, HAS(@cell,\"Customer Experience\"))<\/p>
<\/p>
Check your cross sheet reference {Areas Improved} <\/strong>to make sure it is setup correctly<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":107790,"type":"question","name":"Adding an AND into a formula","excerpt":"Good morning, Community! Current formula (which is working): =IF(CONTAINS(\"APP\", [Employment Type]@row), JOIN([Dropdown Selection]@row:[Employee Status]@row, \"; \")) However, I need to include if the Employee Status equals Active within the above and not sure on the syntax?","snippet":"Good morning, Community! Current formula (which is working): =IF(CONTAINS(\"APP\", [Employment Type]@row), JOIN([Dropdown Selection]@row:[Employee Status]@row, \"; \")) However, I…","categoryID":322,"dateInserted":"2023-07-19T13:21:49+00:00","dateUpdated":null,"dateLastComment":"2023-07-19T14:13:56+00:00","insertUserID":163131,"insertUser":{"userID":163131,"name":"cghallo_UCDenver","title":"Smartsheet Consultant","url":"https:\/\/community.smartsheet.com\/profile\/cghallo_UCDenver","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-19T14:16:10+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-07-19T14:22:50+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":24,"score":null,"hot":3379551345,"url":"https:\/\/community.smartsheet.com\/discussion\/107790\/adding-an-and-into-a-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107790\/adding-an-and-into-a-formula","format":"Rich","lastPost":{"discussionID":107790,"commentID":385923,"name":"Re: Adding an AND into a formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/385923#Comment_385923","dateInserted":"2023-07-19T14:13:56+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-07-19T14:22:50+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/R71AMNDX813P\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-19T13:49:51+00:00","dateAnswered":"2023-07-19T13:46:29+00:00","acceptedAnswers":[{"commentID":385910,"body":"
