How do I total the answers from a drop-down cell?
![BethWork](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
I'm trying to build a dashboard that will show the progress of sites completed for an upcoming upgrade. I have attempted several ways to compile this information automatically and I'm at a loss. I think a simple cell formula might be the easiest way to accomplish what I need then I can link the cell to a report I create.
The cell values are "Not Started" and "Completed". I simply want it to tell me how many cells in the column are at status completed and how many are at status not started.
I have pivot app which I didn't find achieved what I needed as it only let me tally the total number of cells in that column and not the total completed/not started.
Is there a formula that will accomplish what I want?
Best Answer
-
Hi @BethWork
I hope you're well and safe!
Try something like this.
=COUNTIFS(BannerName:BannerName, "Banner Name1", [Store Open]:[Store Open], "Open", [Install Status]:[Install Status], "Not Started")
Did that work/help?
I hope that helps!
Be safe, and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Answers
-
@BethWork If all the data is in one sheet, that is easy. You have a number of ways to do this.
- Create a report of the sheet then summarize by the status column. It will give you the count, and you can create a dashboard widget based on this report that will display the counts
- Use sheet summary fields and the SUMIF formula to count the rows that match your criteria
- Use a metrics sheet to pull in the data and SUMIF it count the rows that meet your criteria
Darren Mullen, smartsheetguru.com
Get my 7 Smartsheet tips here
Author of: Smartsheet Architecture Solutions
-
Darren Mullen, smartsheetguru.com
Get my 7 Smartsheet tips here
Author of: Smartsheet Architecture Solutions
-
I'm attempting option #3 and having the WORST time figuring out the formula.
I need to use a combination of information to achieve what I need.
As an example of what I'm trying to do below: I want to get the total number of Banner Name1 that are Store Open status "Open" and where Install Status is "Not Started". How do I get that formula to work?
I tried this and it did not work for me and came back unparseable.
=COUNTIFS(BannerName:BannerName, "Banner Name1", [Store Open]:[Store Open], “Open”, [Install Status]:[Install Status], "Not Started')
-
Hi @BethWork
I hope you're well and safe!
Try something like this.
=COUNTIFS(BannerName:BannerName, "Banner Name1", [Store Open]:[Store Open], "Open", [Install Status]:[Install Status], "Not Started")
Did that work/help?
I hope that helps!
Be safe, and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
It worked that time. I don't see the difference between the formula I tried and the one you gave me though so I'm not sure why it worked this time. Thank you!
-
Excellent!
Happy to help!
I assume you wrote the formula in word or similar, and then the characters might be wrong. (see below)
Your formula (errors in bold)
=COUNTIFS(BannerName:BannerName, "Banner Name1", [Store Open]:[Store Open], “Open”, [Install Status]:[Install Status], "Not Started')
My formula (corrected errors in bold)
=COUNTIFS(BannerName:BannerName, "Banner Name1", [Store Open]:[Store Open], "Open", [Install Status]:[Install Status], "Not Started")
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Yes, you're right that I did write it in Word. I'll use notepad++ next time. Thank you!
-
You're more than welcome!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!