What formula do I use to add 3 columns together on a metrics sheet?
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
-
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
-
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")
-
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
-
@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)
-
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
-
@kelly906 That's it!!! THANK YOU so much!! You're a lifesaver. These Smartsheet formulas are no joke.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!