Countifs with multiple variables

Shesha_KShesha_K
edited 12/09/19 in Formulas and Functions
04/08/19 Edited 12/09/19

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!

Smartsheet3.PNG

SmartsheetQ.JPG

Comments

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Try something like this...

     

    =COUNTIFS({Joined Column Range}, FIND(FTX$1, @cell) > 0, {Location Column Range}, FIND([email protected], @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.

    thinkspi.com

  • That worked perfectly! Thank you for your help! smiley

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help! yes

    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.

    thinkspi.com

Sign In or Register to comment.