Need Help with COUNTIFS for 3 Columns of different input types
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
-
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)
-
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)
-
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.
Answers
-
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)
-
Excellent! Thanks. It worked great!
-
Happy to help. 👍️
-
@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?
-
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.
-
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)
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.2K Get Help
- 419 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 141 Just for fun
- 58 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 299 Events
- 38 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
