Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    Countifs in multi contact column referencing another sheet - Help!!

    ✭
    in Formulas and Functions

    Please help, i am trying to count the total times each name appears in a multi contact column within 365 days referencing another sheet… where have i gone wrong?

    =COUNTIFS({Archive T&F Date}, <=TODAY(), {Archive T&F Date}, >=TODAY(-365), {Archive T&F Presenter}, FIND("Jen McFarland", @cell) > 0))

    It seems like the @cell bit would work if i were referencing the sheet i were in but i'm really stuck!

    Note, my previous formula worked for all cells where only 1 name was listed but has not counted and where more than one name was listed.

    =COUNTIFS({Archive T&F Date}, <=TODAY(), {Archive T&F Date}, >=TODAY(-365), {Archive T&F Presenter}, "Jen McFarland")

    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Best Answer

    Answers

    • Community Champion
      Answer ✓

      Give this a try:

      =COUNTIFS({Archive T&F Date}, <=TODAY(), {Archive T&F Date}, >=TODAY(-365), {Archive T&F Presenter}, HAS(@cell, "Jen McFarland"))

      · Share on FacebookShare on Twitter
    • ✭

      This seems to be working perfectly, thank you! I shall double check a few totals just to be sure but fingers crossed! Thanks so much.

      · Share on FacebookShare on Twitter
    • Community Champion

      Awesome, I'm glad it is working for you!

      · Share on FacebookShare on Twitter
    • ✭

      So have another question in relation to this… I now need to add another criteria to my formula and have tried every combination i can think of but either get imparseable or incorrect argument. This bit working perfectly:-

      =COUNTIFS({Archive T&F Date}, <=TODAY(), {Archive T&F Date}, >=TODAY(-365), {Archive T&F Presenter}, HAS(@cell, "Jen McFarland"))

      The bit i want to add is

      {Cancelled}, =0,

      Have tried with/without =, in quotation marks, various combinations of false/N/0 but can't get it to work… help!

      (Cancelled is the sheet reference to a column in a separate sheet with a tickbox, so i only want it to count the ones that are not ticked)

      · Share on FacebookShare on Twitter
    • Community Champion

      There could be some text/number inconsistency in your source sheet. Try wrapping the source in the VALUE() tag to force it to be treated as a number.

      =COUNTIFS({Archive T&F Date}, <=TODAY(), {Archive T&F Date}, >=TODAY(-365), {Archive T&F Presenter}, HAS(@cell, "Jen McFarland"), {Cancelled}, VALUE(@cell) = 0)

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions