Need Help with COUNTIFS for 3 Columns of different input types

06/30/20 Edited 06/30/20
Accepted

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

  • Accepted 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 NewcomePaul Newcome ✭✭✭✭✭

    Happy to help. 👍️

    thinkspi.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 NewcomePaul 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.

    thinkspi.com

  • Accepted 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)

Sign In or Register to comment.