Need HELP Creating a Formula

I am trying to create a formula that gives the total of each status from another spreadsheet. All statuses are in one column in the spreadsheet. As you can see, it's not working. PLEASE HELP!



Best Answer

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @Sheila DAniello

    You mentioned another sheet - are the values (the data) you are counting in the other sheet? I understand that the name of the status is on this formula sheet

    Assuming yes, the data is in the other sheet then you will need to insert a cross sheet reference from the formula window. If you need help with this, let me know. If the data is in this sheet's Project status column, use the second formula.

    Data in other sheet. You cannot copy paste this formula, you must insert the reference

    =COUNTIFS({Other sheet data column}, Project Status@row})

    Same sheet data. You can copy paste

    =COUNTIFS([Project Status]:[Project Status], [Project Status]@row)

    Will either of these work for you?

    Kelly

  • Hi Kelly.

    Thanks so much for your help. I'm getting close.

    As you can see, the data is in another sheet and I tried referencing it but still get unparseable. I tried the formulas below. I'm obviously doing something wrong. I even tried selecting the column from the other sheet (see 2nd image). Any thoughts?

    Again, thanks for your help!

    =COUNTIFS([Project Status]:[Project Status], [Project Status]@row)

    =COUNTIFS([Project Status]:Range 1, [Project Status]@row)



  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @Sheila DAniello

    The syntax for the range changes when using cross sheet references vs when using a range on the same sheet. The first formula I gave you showed that syntax.

    =COUNTIFS({Other sheet data column}, Project Status@row})

    After inserting your cross sheet reference, will this formula work for you?

    Kelly

    If desired, you can find more info on cross sheet references in the link below


  • Hi Kelly.

    It's Sheila again. Is there any way I can call you? I have to present my data on Wednesday and really am not understanding why it's not working. I think 5 minutes should be good. I can send my number if you prefer.

    Thank you. 😊

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    hey Sheila

    Sure, you can send your number- I'm free now. or, we can zoom - whatever works.

    Kelly

  • OMG! Thank you. I can Zoom or Teams. My email is sheila.daniello@nm.org.


    THANK YOU!!

  • Thanks Kelly. You're absolutely awesome!!

    Sheila

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!