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.