Calculating Dates and Values

Sinema

I have a column that contains dates and I want to be able to make a graph showing how many orders were imported on a specific date.

Is that possible in Smartsheet?

Example

Location A would show that it had 39 orders on January 1st, 15 orders on January 2nd etc.

Location B .. .etc..

I would like to be able to have Location A in one column, 39 in 2nd column and the date in the third column.

I can do that in excel and any reporting tool but I can't seem to get that to work in Smartsheet.

I tried "CountIFS" but you have to put a lot of coding to do anything with dates it seems.

Chris Mondeau

Hi Sinema,

You can do this with the Pivot App if you have that from Smartsheet. Alternatively you could also achieve this by using a combination of multiple sheets with cross-sheet formulas, or by using the sheets summary to total for a range.

Here's an example of using a location and year specific sheet with the sheet summary:

The formula for the months in the sheet summary are:

=IFERROR(SUMIF(Date:Date, MONTH(@cell) = 1, Orders:Orders), 0)

You'd need a sheet per location, per year for each order tracker, a sheet summary report to collect all locations and their monthly totals, then you can put those into a dashboard.

You can also achieve a similar result by using a combination of hierarchy where you roll up the quantities with =SUM(CHILDREN()) in the parent row's order column.

Paul Newcome

All you need to do is compile this information using COUNTIFS either on the same sheet or a single different sheet. You very likely will not need multiple sheets (other than maybe the one Metrics Sheet) or Sheet Summaries. You could very possibly have all locations with all dates listed on one sheet depending on how many dates/locations you are tracking vs the 5,000 row and 200 column limits. This will keep all of the data in one place so that it is easy to find when building out your charts on your dashboard.

The coding to incorporate dates can actually be pretty straightforward depending on your exact needs.

Are you able to provide a screenshot with the data manually entered to reflect how you want your final result to be displayed?