Using COUNTIFs with FIND & AND
Hi,
I need to add up the following:
1) All Labeled as Country X from a Qualitative field that is requiring me to use the FIND function.
2) All labeled as AreaY across 3 columns (I have tried selecting all columns as a range and also just selecting the relevant rows as well).
Currently using:
=COUNTIFS({Consultant Applicants Range 4}, "AreaY", AND(FIND({Consultant Applicants Range 2}, "CountryX", @cell) > 0))
Showing up as unparseable. Please help!
Comments
-
When using a COUNTIFS function, the AND is implied. All range/criteria sets must be true (with very few exceptions). Another thing to keep in mind is that all of the ranges need to be of the same size. So if Range 1 is a single column, range 2 must also be a single column.
I would suggest adding an additional column to the data sheet (I'll call it "Helper" for this example). You would then use a basic JOIN formula to pull together all of the Area columns.
=JOIN([First Area Column]:[Last Area Column], ", ")
This will give you the ability to keep your ranges the same (a single column) while searching across all three of the Area columns using the FIND function.
Your COUNTIFS would then look something like this...
=COUNTIFS({Helper Column Range}, FIND("AreaY", @cell) > 0, {Country Column Range}, FIND("CountryX", @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!
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 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