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("InProcess", @cell)))
Answers

@Mike13 What type of columns are the {Sub Type Update} range and {PhaseForecast} range referencing? Plain Text/Number or multiselect dropdown list?
For multiselect 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 'InProcess'; 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, "InProcess"))))), =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!

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

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, "InProcess")), 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 "InProcess" (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)
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!
Help Article Resources
Categories
Check out the Formula Handbook template!