Using COUNTIFs with FIND & AND

wajeeha.mh
edited 12/09/19 in Smartsheet Basics

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com