I'm trying to calculate the number of instances where only two items appear in a cell.

I'm trying to calculate the number of instances where only two items (the words content and functionality) appear in a cell. My current formula is not pulling the correct count. Any suggestions? P.S. I'm a formula rookie :)

=COUNTIFS({Sub Type Update}, "Functionality", AND "Content", {PhaseForecast}, OR(CONTAINS("Planned", @cell), CONTAINS("In-Process", @cell)))

Find more posts tagged with

Formulas

Accepted answers

All comments

Jeff Reisman

@Mike13 What type of columns are the {Sub Type Update} range and {PhaseForecast} range referencing? Plain Text/Number or multi-select dropdown list?

For multi-select fields, I'm thinking your logic might be something like this:

Determine the rows where Sub Type Update contains both 'Content' and 'Functionality', where the same row contains either 'Planned' or 'In-Process'; Of those, determine the number of distinct values in the Sub Type Update cell and count the instances where that number is 2.

So you may use something like:

=COUNTIFS(COUNT(DISTINCT(COLLECT({Sub Type Update}, {Sub Type Update}, HAS(@cell, "Functionality"), {Sub Type Update}, HAS(@cell, "Content"), {PhaseForecast}, OR(HAS(@cell, "Planned"), HAS(@cell, "In-Process"))))), =2)

I haven't tested this anywhere. Check parentheses to make sure they line up in Smartsheet. Logically this should work, but your mileage may vary. Good luck!

Mike13

Yes, it's a multi-select dropdown list. Unfortunately, this did not calculate correctly. It returned 0 (should have been 91). Thank you for your help!

Jeff Reisman

https://community.smartsheet.com/discussion/comment/415277#Comment_415277

Can you use a helper column in your source sheet to identify the rows that meet the criteria? I use workarounds like this all the time in place of more complex formulas. The formula in my helper column adds 1 for every criteria met:

=IF(AND(HAS(SubTypeUpdate@row, "Content"), HAS(SubTypeUpdate@row, "Functionality")), 1) + IF(OR(HAS(PhaseForecast@row, "Planned"), HAS(PhaseForecast@row, "In-Process")), 1) + IF(COUNTM(SubTypeUpdate@row) = 2, 1)

If the SubTypeUpdate column has both "Content" and "Functionality" in it, that's 1.

If PhaseForecast has "Planned" or "In-Process" (or both), add 1 more.

If the count of the elements (the COUNTM function) in the SubTypeUpdate column equals 2, add another 1. (This excludes any row that has more than Content and Functionality in that column.)

Any row that meets all the criteria will have a value of 3 in the helper column. From your remote sheet, simply count the rows in the helper column that are equal to 3. =COUNTIF({HelperST}, =3)

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.