Using COUNTIFs with FIND & AND

wajeeha.mh

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!

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)