Formula/pie chart help?
I'd like to have a pie chart that shows a dynamic count of orders "pending/fulfilled/cancelled" of the last seven days. Ideally, there would be four separate ones for four locations (which will be presented on their personal dashboards), as well as a master graph that compiles the data for all four into one for upper management's viewing.
My first attempt has me using =count functions to count the total number of the pending/fulfilled/cancelled on a different sheet, but I'm not sure how to work the "last seven days" into my formula.
I was thinking it would involve specifying my criteria through vlookup, but I'm inexperienced with anything past basic formulas and am unsure how to continue. If there is a more efficient solution, I am open to that as well!
All help is appreciated, thanks!
Best Answer
-
Hello @Jennifer Castillo
I feel the easiest method to achieve this would be to create a COUNTIFS Formulas in the Summary Fields of your Sheet, then create a Summary Report, pulling the different status types and then use this in a Summary Report for your Pie Chart Widget. It may look like this:
Sheet:
Sheet Summary:
Sheet Summary Report:
Dashboard:
The Formula I used to do this on the same Sheet was =COUNTIFS(Status:Status, "Pending", Date:Date, >=TODAY(-7)) whilst modifying the Status name within the quotation marks.
If you were to have a Master Sheet to record all the Status Types from different Sheets, the Formula would could like this, depending how you record them in your Master Sheet: =COUNTIFS({Status}, "Pending", {Dates}, >=TODAY(-7)).
Alternatively, if you had a Master Sheet that listed the Sale Location, and had the Status below itself, your Sheet and Formula may look like this:
Formula for Count: =COUNTIFS({Status}, [Sales Status]@row, {Dates}, >=TODAY(-7))
Formula to SUM total next to Location: =SUM(CHILDREN())
As you can see, there are many different ways to set this up to achieve your desired result. With this, here are the articles used to create this solution:
- COUNTIFS: https://help.smartsheet.com/function/countifs
- Cross Sheet Formulas: https://help.smartsheet.com/learning-track/smartsheet-advanced/cross-sheet-formulas
- SUM: https://help.smartsheet.com/function/sum
- Sheet Summary: https://help.smartsheet.com/learning-track/smartsheet-intermediate/sheet-summary
- Sheet Summary Reports: https://help.smartsheet.com/learning-track/smartsheet-intermediate/sheet-summary-reports
- Hierarchy: https://help.smartsheet.com/articles/504734-hierarchy-indenting-outdenting-rows
Furthermore, you may want to reach out to our Pro Desk team, and enquire around 1 to 1 coaching. This is where you can choose your desired topic, and one of our specialised agents will provide a Screen Share coaching session, and can assist creating a solution for you. See more on this here: https://help.smartsheet.com/articles/2478411-get-coaching-pro-desk-info
Let me know if you have any questions!
Regards
Sean
Answers
-
Hello @Jennifer Castillo
I feel the easiest method to achieve this would be to create a COUNTIFS Formulas in the Summary Fields of your Sheet, then create a Summary Report, pulling the different status types and then use this in a Summary Report for your Pie Chart Widget. It may look like this:
Sheet:
Sheet Summary:
Sheet Summary Report:
Dashboard:
The Formula I used to do this on the same Sheet was =COUNTIFS(Status:Status, "Pending", Date:Date, >=TODAY(-7)) whilst modifying the Status name within the quotation marks.
If you were to have a Master Sheet to record all the Status Types from different Sheets, the Formula would could like this, depending how you record them in your Master Sheet: =COUNTIFS({Status}, "Pending", {Dates}, >=TODAY(-7)).
Alternatively, if you had a Master Sheet that listed the Sale Location, and had the Status below itself, your Sheet and Formula may look like this:
Formula for Count: =COUNTIFS({Status}, [Sales Status]@row, {Dates}, >=TODAY(-7))
Formula to SUM total next to Location: =SUM(CHILDREN())
As you can see, there are many different ways to set this up to achieve your desired result. With this, here are the articles used to create this solution:
- COUNTIFS: https://help.smartsheet.com/function/countifs
- Cross Sheet Formulas: https://help.smartsheet.com/learning-track/smartsheet-advanced/cross-sheet-formulas
- SUM: https://help.smartsheet.com/function/sum
- Sheet Summary: https://help.smartsheet.com/learning-track/smartsheet-intermediate/sheet-summary
- Sheet Summary Reports: https://help.smartsheet.com/learning-track/smartsheet-intermediate/sheet-summary-reports
- Hierarchy: https://help.smartsheet.com/articles/504734-hierarchy-indenting-outdenting-rows
Furthermore, you may want to reach out to our Pro Desk team, and enquire around 1 to 1 coaching. This is where you can choose your desired topic, and one of our specialised agents will provide a Screen Share coaching session, and can assist creating a solution for you. See more on this here: https://help.smartsheet.com/articles/2478411-get-coaching-pro-desk-info
Let me know if you have any questions!
Regards
Sean
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!