Add Yes's in columns where the row equals a specific name

I am trying to add all the Yes's in rows where the one column = a service name.

Show me the count for all Yes's where the service name = "Data Protection"

=COUNTIFS({QA YES Range},"Yes",[{QA DP Value Column},"DATA PROTECTION"])

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    @deeave

    Looks to me like you need to remove these square brackets:

    If you're still getting an error after that, please share the error message, and the column types involved, sampling of data, if possible. The more screenshots the better.

    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!

  • deeave
    deeave ✭✭

    @Jeff Reisman Incorrect Argument Set is received when removing the brackets and using the formula below.

    Current Formula: =COUNTIFS({QA YES Range}, "Yes", {QA DP Value Column}, "DATA PROTECTION")


    This is a sample of the data.


  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    @deeave

    Does the {QA YES Range} include all the columns in yellow?

    If so, that brings up two approaches, depending on what you want.

    If you just want to count all the rows that include a "Yes" anywhere in the QA Yes Range, then your formula should work, probably just need to throw @cell into the criteria:

    =COUNTIFS({QA YES Range}, @cell = "Yes", {QA DP Value Column}, @cell = "DATA PROTECTION")

    If we were just considering the rows in your screenshot, the above should return an answer of 4.

    If you want to count all the instances of "Yes" (3 "Yes"es in the first row + 2 "Yes"es in the second row + 1 "yes" in the third row, + 1 "Yes" in the fourth row), then we need to do this a bit differently. You'll need to reference each column individually from your formula, and essentially add up multiple COUNTIFS:

    =COUNTIFS({Active Passed Finish Date Range}, @cell = "Yes", {QA DP Value Column}, @cell = "DATA PROTECTION") + COUNTIFS({14 Day Schedule Published Range}, @cell = "Yes", {QA DP Value Column}, @cell = "DATA PROTECTION") + COUNTIFS({Risk Due Date Passed Status New Range}, @cell = "Yes", {QA DP Value Column}, @cell = "DATA PROTECTION") and so on...

    Based on your screenshot, the above formula completed for all "Yes" columns would return an answer of 7.

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!