Countifs with multiple variables

Shesha_K

I am trying to automate a tally of which SME's have worked an event with which countries. Ultimately,  I created a column that lists the countries that participated in the particular event (ideally I would like to keep multiple countries in one cell per event), and hoped to use the "joined" column to reference the SME's designating to count only if the Tally or Not column is checked. I have tried:

=COUNTIFS({Past Events Range 2}, FIND(FTX$17) > 0, {Past Events Range 1}, FIND(SME18) > 0, {Past Events Range 4}, 1)

=COUNTIFS({Past Events Range 2}, FIND(FTX$17) > 0, {Past Events Range 1}, SME18, {Past Events Range 4}, 1)

Any help with this is greatly appreciated!

Paul Newcome

Try something like this...

 

=COUNTIFS({Joined Column Range}, FIND(FTX$1, @cell) > 0, {Location Column Range}, FIND($SME@row, @cell) > 0)

 

NOTE: The $ is used to lock in row or column references. I have included them in the above formula so that you can enter it into the top left cell of your table and then drag-fill down and over. This allows for more flexibility as you won't have to edit the formula for each variable.

Shesha_K

That worked perfectly! Thank you for your help! 

Paul Newcome

Happy to help! 

When using the FIND function, you NEED two parts.

1. What you are looking for.

2. Where to look.

In your formulas, you were only saying what to look for. You simply needed an @cell reference to tell the formula where.