What formula do I use to add 3 columns together on a metrics sheet?

Options

I am trying to take 3 different columns and count specific instances (Not Met, Met, and Surpassed) on a metrics sheet so I can create a graphic representation on a dashboard. However, I tried a COUNTIFS formula and it kept saying 0 for all when I know that's not correct. I even added AND and that didn't work.


Please help!!

Best Answer

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

    As I mentioned in the response above, the COUNTIFS is looking for rows that have "Not Met" in all 3 columns. To get the total of the 3 columns independently you will need 3 COUNTIFS

    IF(COUNTIFS({Project Unify Functional KT Tracker Range 10},"Grants Management")>0, COUNTIFS({Project Unify Functional KT Tracker Range 7}, "Not Met")+COUNTIFS({Project Unify Functional KT Tracker Range 8}, "Not Met")+COUNTIFS({Project Unify Functional KT Tracker Range 9}, "Not Met"))

    Is this what you needed?

    Kelly

Answers

  • JSabillon
    JSabillon ✭✭
    edited 09/14/22
    Options

    I thought this might work, but it just keeps saying "unparseable" -


    =IF({Project Unify Functional KT Tracker Range 10}, "Grants Management", "TRUE") COUNTIF({Project Unify Functional KT Tracker Range 7}, "Not Met", {Project Unify Functional KT Tracker Range 8}, "Not Met", {Project Unify Functional KT Tracker Range 9}, "Not Met")

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Hey @JSabillon

    Screenshots always help the community better understand what you're trying to accomplish. I'll take a guess at what you were attempting

    IF(COUNTIFS({Project Unify Functional KT Tracker Range 10},"Grants Management")>0, COUNTIFS({Project Unify Functional KT Tracker Range 7}, "Not Met", {Project Unify Functional KT Tracker Range 8}, "Not Met", {Project Unify Functional KT Tracker Range 9}, "Not Met"))

    This says if "Grants Management" is found anywhere in the {Project Unify Functional KT Tracker Range 10} range then Count the rows where those three columns simultaneously all have "Not Met" in the columns. Is this what you were trying to do?

    Kelly

  • JSabillon
    JSabillon ✭✭
    edited 09/14/22
    Options

    @kelly906 , yes. So IF "Grants Management" is found in the column range, I want it to then count 3 different columns to give me a total for all 3 testing sessions combined - it will be a running total until the project is complete. I'm trying to create the following bar graph with my data:

    (only the top one - not concerned with the bottom one)

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

    As I mentioned in the response above, the COUNTIFS is looking for rows that have "Not Met" in all 3 columns. To get the total of the 3 columns independently you will need 3 COUNTIFS

    IF(COUNTIFS({Project Unify Functional KT Tracker Range 10},"Grants Management")>0, COUNTIFS({Project Unify Functional KT Tracker Range 7}, "Not Met")+COUNTIFS({Project Unify Functional KT Tracker Range 8}, "Not Met")+COUNTIFS({Project Unify Functional KT Tracker Range 9}, "Not Met"))

    Is this what you needed?

    Kelly

  • JSabillon
    Options

    @kelly906 That's it!!! THANK YOU so much!! You're a lifesaver. These Smartsheet formulas are no joke.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!