Average formula for text field

Hi I’m new to smartsheet

I am trying to input a formula into the sheet summary field that will average “closed complete” and “closed cancelled” for a column labeled “status”

I was able to count the two fields for both but not average them with the proper formula

thank you

Best Answer


  • Nik Fuentes
    Nik Fuentes ✭✭✭✭✭

    I image the intended outcome is to have it read "Closed Complete" if there are more of those and "Closed Cancelled" if there are more of those, right? if so, you'll want a nested if() statement instead of an avg().

    try something like

    if(Count("Closed Complete")>Count("Closed Cancelled"),"Closed Complete", if(Count("Closed Cancelled")>Count("Closed Complete"),"Closed Cancelled","Something Else"))

    E.G.:: if there are more "closed complete" than "closed cancelled," say "closed complete," otherwise; if there are more "closed cancelled" than "closed complete," say "closed cancelled," otherwise say 'something else."

  • Thank you Nik, I was hoping to get the % of those tickets that are close complete and % of closed cancelled out of the total column for Status because there will be times there will be an “open” status in future reports that I want to have to show % Of out of the total so I wanted to understand how to do this with the data on this report. I am going to try this that you sent, but I’m not sure if it will produce what I’ve been asked to show. Im so knew it’s taking me a long time to figure out these types of formulas or if I just need to build a report with the summary of that column as average ?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    First you count the closed and then divide by the count of the total.

    =COUNTIFS(Status:Status, "Closed Complete") / COUNT(Status:Status)

  • @Paul Newcome do you do or know of anyone on here that does smartsheet tutoring or can walk me through a sheet / report/ dashboard set up due to the number of hours it’s taking me to figure all this out I rather walk through it with someone the first few times to reduce time.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I am not sure of anyone that does it for free. I do know that Smartsheet offers professional services for a cost. I provide professional services for a cost as well.

    There are a number of free articles and videos in the learning center and HERE is a collection of onboarding videos/lessons that SS offers for free.

  • @Paul Newcome I am looking to pay for tutoring services. I am walking through the free videos and onboarding but what I need to produce I am thinking working with a tutor is best. If you are available please let me know how to contact

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I don't offer tutoring services. We do consultancy work (full solution builds/business practices evaluation/etc.). I am not sure of anyone who does offer tutoring.

    @Genevieve P. Does Smartsheet offer something that @J25 could take advantage of?

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @J25

    Yes! We have a number of services that may be helpful for you.

    The first thing I would check is to see if your plan already has access to booking Pro Desk sessions. These are 30-minute coaching sessions over a screen share where you can ask your questions and receive direction 1-on-1 with a Smartsheet expert. (See: Pro Desk)

    Smartsheet also has a number of Webinars that you can register for, or review any of the past recorded Webinars (SmartStart Onboarding / Smartsheet Advance Webinars / Best Practice Webinars).

    Your plan may also have access to the Smartsheet University, where you can go through eLearning courses or Instructor-led training.



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!