How do you apply the =SUM(COUNTIFS(DECENDANTS()) functions with 3 or more criteria?

Options
Alison Laninger
edited 03/09/20 in Formulas and Functions

I am using the =SUM(COUNTIFS(DECENDANTS()) functions and have successfully applied it for 1 criteria set with the following..


=SUM(COUNTIFS(DESCENDANTS([Contract Type Code]764), "TCH", DESCENDANTS([Instructor Type Lookup]764), "Staff"))


and also successfully applied it for 2 criteria set with the following...


=SUM(COUNTIFS(DESCENDANTS([Contract Type Code]764), "TCH", DESCENDANTS([Instructor Type Lookup]764), "FT2"), (COUNTIFS(DESCENDANTS([Contract Type Code]764), "TCH", DESCENDANTS([Instructor Type Lookup]764), "IC")))


but have not been able to replicate this formula when 3 or more criteria is required to produce the information i need.


How do I apply these functions with 3 or more criteria when using COUNTIFS?

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 03/09/20
    Options

    What is the desired result? Are you trying to add multiple COUNTIFS together? Are you getting an error or an unexpected result?


    Right off I would say try this and see if it corrects the problem. If it does not, I would need more detail as to exactly what you are trying to accomplish.

    =SUM(COUNTIFS(DESCENDANTS([Contract Type Code]764), "TCH", DESCENDANTS([Instructor Type Lookup]764), "FT2"), COUNTIFS(DESCENDANTS([Contract Type Code]764), "TCH", DESCENDANTS([Instructor Type Lookup]764), "IC"))


    EDIT:

    Give this a try first...


    =COUNTIFS(DESCENDANTS([Contract Type Code]764), "TCH", DESCENDANTS([Instructor Type Lookup]764), OR(@cell = "FT2", @cell = "IC"))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!