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

Mike13
Mike13 ✭
in Formulas and Functions

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)))

Tags:
· Share on FacebookShare on Twitter

Answers

  • Jeff Reisman
    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!

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

    · Share on FacebookShare on Twitter
  • Mike13
    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!

    · Share on FacebookShare on Twitter
  • Jeff Reisman
    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)

    image.png

    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)

    image.png


    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!