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

JSabillon

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!!

Kelly Moore

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

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

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

@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

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

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