Need Help with COUNTIFS for 3 Columns of different input types

Christopher Loh

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!

Paul Newcome

You would just want to include the range/criteria in the original COUNTIFS following the same syntax.

=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)

Christopher Loh

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)

Paul Newcome

If the columns [Headcount Type], [FTE or Contractor], and Leader are on the other sheet, then you need to create separate cross sheet references for them.

Paul Newcome

You would just want to include the range/criteria in the original COUNTIFS following the same syntax.

=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)

Christopher Loh

Excellent! Thanks. It worked great!

Paul Newcome

Happy to help. 👍️

Christopher Loh

https://community.smartsheet.com/discussion/comment/255193#Comment_255193

@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

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.

Christopher Loh

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)

Paul Newcome

If the columns [Headcount Type], [FTE or Contractor], and Leader are on the other sheet, then you need to create separate cross sheet references for them.