COUNTIF + MATCH? between multiple sheets

Lisa Matthews
Lisa Matthews ✭✭✭✭✭
edited 04/28/23 in Formulas and Functions


I'm needing help with another formula. This is my objective:

  1. Match the [Location ID#] between Sheets A & B.
    1. Sheet "A" contains one row per location.
    2. Sheet "B" contains multiple rows per location.
  2. Then, count the number of "Yes" values in Sheet B for each location and return the count total to Sheet A for each location (row).

Thank you in advance.

--Lisa M.


  • Deric
    Deric ✭✭✭✭✭
    1. Create a helper column on Sheet B: [Joined Column]
    2. Create a column formula in [Joined Column]: =JOIN([Location ID#]@row:[OK to Disco?]@row, "-")
    3. Create a cross-sheet reference on Sheet A for Sheet B [Joined Column]: {SheetB_Joined}
    4. Create a column formula in Sheet A [# of lines ok to disconnect]: =COUNTIF({SheetB_Joined}, = ([Lines Converted]@row + "-YES"))
  • Lisa Matthews
    Lisa Matthews ✭✭✭✭✭

    Hi @Deric

    I'm still having some trouble with this solution. I had to stop working on it for a bit but will get back to it over the weekend. Thank you for your help.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!