Need Assistance with my Countifs OR

Options
Claudia G
Claudia G ✭✭
edited 10/24/23 in Formulas and Functions

I have read other Q/As and tried multiple options, but keep getting #unparseable errors.

Basically, I have a table I have created at the bottom of my spreadsheet for reporting purposes. I want to count how many open items have the accountable person match the name in the "Details/Description" column of the current row of my spreadsheet.

This works:

=COUNTIFS(Status:Status, <>"Complete", Status:Status, <>"Not Needed", Accountable:Accountable, [Details/Description]@row)

However, some items have 2 accountable people, so I want to check for a match in either the Accountable column or the Accountable2 column. This is one of the formulas I have tried that is not working

=COUNTIFS(Status:Status, <>"Complete", Status:Status, <>"Not Needed", OR([Details/Descriptions]@row,Accountable:Accountable, [Details/Description]@row,Accountable2:Accountable2))

Even tried this after seeing the + option on a Youtube video. It does not work either:

=COUNTIFS((Status:Status, <>"Complete", Status:Status, <>"Not Needed", Accountable:Accountable, [Details/Description]@row) +(Status:Status, <>"Complete", Status:Status, <>"Not Needed", Accountable2:Accountable2, [Details/Description]@row))

Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Options

    Hi @Claudia G,

    Something like this?

    =COUNTIFS(Status:Status, AND(@cell <> "Completed", @cell <> "Not Needed"), [Details/Description]:[Details/Description], OR(@cell = Accountable@row, @cell = [Accountable2]@row))

    Count if: Status is not "Completed" or "Not Needed" and Accountable or Accountable2 contents match Details/Description.

    Hope this helps, but if I've misunderstood something or you have any problems/questions then just post! 🙂

  • Claudia G
    Claudia G ✭✭
    edited 10/24/23
    Options

    Thank you. At least that formula was accepted, but it is not returning the correct result (the field I tested should have just increased by 2 when I included Accountable2, but in increased by 6 instead. Also, when I copied to other field in the table the number of matches found for each row was the same (and they are all different in actuality)

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Are you able to provide screenshots for context?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!