Countifs with find

edited 12/09/19 in Using Smartsheet
10/14/19 Edited 12/09/19

I have 2 columns  (Stage) and (Courses) Courses is a multi dropdown, stage is a single drop down. 



I want to count how many people are invited to Fundamentals 1 vs how many have completed or certified in a rollup sheet. I am either getting unparsable or invalid 

 

This is on a rollup sheet that points to the working sheet. 

=COUNTIFS({Splunk Training course}, FIND("Fundamentals 1", @cell) > 0, {Splunk Training Stage}(Invited))



Thanks in advance. 

Comments

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Can you explain the BOLD portion of your formula?

    =COUNTIFS({Splunk Training course}, FIND("Fundamentals 1", @cell) > 0, {Splunk Training Stage}(Invited))

  • Yes.  This is tracking training, I have a column for the status (Invited, Registered, completed, etc) and another column with a multi checkbox (Fundamentals 1, Fundamentals 1, Dashboards) So I need to count how many are invited for fund 1, then invited for fund 2, then in another cell is how many are completed or certified in fund 1. 

     

    I hope that clears it up. 

     

    Thanks

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    That does clear things up. It also means you were very close with your formula.

     

    Here it is with a few minor tweaks...

     

    =COUNTIFS({Splunk Training course}, FIND("Fundamentals 1", @cell) > 0, {Splunk Training Stage}, "Invited")

     

    We added a comma between the stage range and the stage to separate range from criteria then we replaced the parenthesis around Invited with quotes.

  • Looks like this does the trick, thank you, formulas are probably my weak point which is maddening when I am rolling up data. :)

     

    Thank you

     

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help! yes

     

    The Community is a great source of knowledge. Don't hesitate to reach out. It is certainly much less painful than beating your head against a wall for a few days (or even weeks).

Sign In or Register to comment.