Need Help with COUNTIFS for 3 Columns of different input types

Christopher Loh
edited 06/30/20 in Formulas and Functions

I have 3 columns with different input types (Contact List, Multi select Dropdown, and Single Select Dropdown). I want to do a count for criteria that satisfies 3 criteria (1 for each column). I have figured out how to do it for 2 criteria but cant get it to count for 3 criteria.



My code for 2 criteria is as follows:

=COUNTIFS([Headcount Type]:[Headcount Type], CONTAINS("Existing HC", @cell), [FTE or Contractor]:[FTE or Contractor], CONTAINS("FTE", @cell))

Above is working great and took me hours to figure out.

Now I want to include the LEADER column to include in the count if it includes the Leader Name.

=COUNTIFS(Leader:Leader, FIND("Christopher Loh", @cell) > 0)

Thanks!

Best Answers

  • Christopher Loh
    Answer ✓

    Yes. The formula works great if I use the syntax on the same page as the columns

    =COUNTIFS([Headcount Type]:[Headcount Type], CONTAINS("Existing HC", @cell), [FTE or Contractor]:[FTE or Contractor], CONTAINS("FTE", @cell), Leader:Leader, FIND("Christopher Loh", @cell) > 0)

    It gives me #UNPARSEABLE if I put it on a separate Smartsheet and reference back to the other page

    =COUNTIFS({DLT HC Tracker}, [Headcount Type]:[Headcount Type], CONTAINS("Existing HC", @cell), [FTE or Contractor]:[FTE or Contractor], CONTAINS("FTE", @cell), Leader:Leader, FIND("Christopher Loh", @cell) > 0)

Answers

  • Excellent! Thanks. It worked great!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • @Paul Newcome I'm trying to perform this count on another sheet where I want to roll up all the counts and reference back to the cells on the original sheet which is called "DLT HC Tracker" but I keep receiving an #UNPARSEABLE error. I used the following syntax to refer back to the other sheet: =COUNTIFS({DLT HC Tracker}, [Headcount Type]:[Headcount Type], CONTAINS("Existing HC", @cell), [FTE or Contractor]:[FTE or Contractor], CONTAINS("FTE", @cell), Leader:Leader, FIND("Christopher Loh", @cell) > 0)

    I've also tried variations and even a simpler format of the syntax without any success. Can you point me in the right direction?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Are the columns you are referencing also on the other sheet? If so, you would create separate cross sheet references for each range and select the appropriate column header when creating the range.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Christopher Loh
    Answer ✓

    Yes. The formula works great if I use the syntax on the same page as the columns

    =COUNTIFS([Headcount Type]:[Headcount Type], CONTAINS("Existing HC", @cell), [FTE or Contractor]:[FTE or Contractor], CONTAINS("FTE", @cell), Leader:Leader, FIND("Christopher Loh", @cell) > 0)

    It gives me #UNPARSEABLE if I put it on a separate Smartsheet and reference back to the other page

    =COUNTIFS({DLT HC Tracker}, [Headcount Type]:[Headcount Type], CONTAINS("Existing HC", @cell), [FTE or Contractor]:[FTE or Contractor], CONTAINS("FTE", @cell), Leader:Leader, FIND("Christopher Loh", @cell) > 0)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!