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)

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

Try this:

=COUNTIFS({Responsible}, HAS(@cell, $Label@row), {January}, "ON")

All comments

Paul Newcome

Try this:

=COUNTIFS({Responsible}, HAS(@cell, $Label@row), {January}, "ON")

Amber Bodaly

Thank you @Paul Newcome!!! that worked :)

Paul Newcome

Happy to help. 👍️

FrogHolder_01

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!

Genevieve P.

Hi @FrogHolder_01

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.