Trouble with count and collect function when multiple values are within a cell
Hello, I am trying to count the times that a department is responsible for a task in the month of January from other sheets. I'm having trouble counting when there are multiple values within a cell. It will only count when the "label" is on its own so I'm thinking I need to add in a contains or has function but am having trouble do so.
=COUNT(COLLECT({Project Status}, {Responsible}, $Label@row, {January}, "ON"))
I've used a function within the RACI Matric sheet to determine whether the task is within the month of January (the January column referenced)
Best Answer
-
Try this:
=COUNTIFS({Responsible}, HAS(@cell, $Label@row), {January}, "ON")
Answers
-
Try this:
=COUNTIFS({Responsible}, HAS(@cell, $Label@row), {January}, "ON")
-
Thank you @Paul Newcome!!! that worked :)
-
Happy to help. 👍️
-
Hoping to get in on the Help Parade here. Fairly new to Smartsheet. I'm tracking programs that are sometimes taught by one colleague, and sometimes by a team of 3 - 5. I want to Report to my colleagues the Counts and Attendance Totals of the programs they taught, regardless of whether they were solo or not.
I've created COUNTIFS and SUMIFS formulae that work perfectly for the solo programs. But I'm getting returns of "0" (not errors) whenever there is more than one person. One tricky thing is that the staff info isn't consistent. When there's multiple staff, the delineators are sometimes carriage returns, and sometimes commas, so my data is a mix of:
John Lennon
Paul McCartney
Ringo Starr
&
John Lennon, Paul McCartney, Ringo Starr
but the formula fails in either case.
I've been trying to use HAS instead of CONTAINS based on the advice in this Forum. I would swear the formula is properly formatted:
=SUMIFS({MP Master Attendance Sheet 2022 Range 3}, {2022 MS Staff}, HAS(@cell, $[Column5]$1), {2022 MS Type}, [Primary Column]@row)
where {2022 MS Staff} is the range of staff data I'm searching, and $[Column5]$1 is an absolute field reference in the same sheet (with the name of the staff I'm searching for. It doesn't matter if I substitute the name as a string instead--still fails).
Apologies if this has an answer elsewhere in the form. I went through a few dozen search results before trying this. Grateful for any and all suggestions on what I'm doing wrong!
-
If the 2022 MS Staff column isn't multi-select and is a text column, CONTAINS actually might work well here. It will search to see if the cell contains your text amongst other text. As long as your names are unique enough, this should work.
Your structure is correct! Try swapping around HAS for CONTAINS, like so:
=SUMIFS({MP Master Attendance Sheet 2022 Range 3}, {2022 MS Staff}, CONTAINS($[Column5]$1, @cell), {2022 MS Type}, [Primary Column]@row)
If this hasn't worked, double check that the "MS TYPE" match with your Primary Column value is exactly the same. If this still hasn't helped, it would be useful to see screen captures of both of your sheets, but please block out sensitive data!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!