Stacked bar chart formula using COUNTIFS and HAS functions

Options

I have created a helper sheet to use for building a stacked bar chart. The sheet data should count how many open tasks an individual is assigned to from two different sheets. Because there may be multiple contacts in an Assigned To column, I need to update this formula by adding HAS. I cannot get it to work correctly in this helper sheet.

My formula:

=COUNTIFS({Network General Operations Tasks Assigned To}, HAS($Person@row, {Network General Operations Tasks Status}, <>"Complete"))


The formula is delivering "0" as a result for Brian, when it should be 13 based on the sheet summary formula from the source sheet (Network General Operations Tasks):

Any ideas on what I'm missing here? Appreciate it!

Tags:

Best Answer

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓
    Options

    Ah yes, one other thing missing - the range for the HAS function.

    =COUNTIFS({Network General Operations Tasks Assigned To}, HAS(@cell,$Person@row), {Network General Operations Tasks Status}, <>"Complete")

Answers

  • KPH
    KPH ✭✭✭✭✭✭
    Options

    Hi@Laura L

    It looks like the closing parenthesis for your HAS is in the wrong place. Try moving it to the end of the first criteria (in bold here), instead of at the end of the formula.

    =COUNTIFS({Network General Operations Tasks Assigned To}, HAS($Person@row), {Network General Operations Tasks Status}, <>"Complete")

    Also FYI - your screen shot above shows Brian's full name in the formula. I think you can edit the post.

  • Laura L
    Laura L ✭✭✭
    Options

    Thanks for your quick reply! I made the change you suggested and now I am getting an Incorrect Argument Set error.

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓
    Options

    Ah yes, one other thing missing - the range for the HAS function.

    =COUNTIFS({Network General Operations Tasks Assigned To}, HAS(@cell,$Person@row), {Network General Operations Tasks Status}, <>"Complete")

  • Laura L
    Laura L ✭✭✭
    Options

    This works beautifully, thank you so much!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!