Formula/pie chart help?

Jen Castillo

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!

Sean Morgan

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