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)
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!
-
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.
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!
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)
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!
-
Excellent! Thanks. It worked great!
-
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!
-
@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.
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!
-
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.
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 213 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!