Totals By Date

Chase Arthur

Hello,

Has anyone successfully used dates to pull totals?

I'm trying to do the following:

Columns: Start Date | End Date | Status

                1/2/18           1/10/18     Completed

                1/5/18           1/7/18       Completed

                1/5/18                               Open

                2/8/18           2/15/18     Completed

                2/16/18          2/20/18    Cancelled     



Output: Total 'Completed' for January 2018 =

 

I'm wanting to use the dates and status to pull different totals. So if I would like to be able to total different criteria based on the dates, is this possible?

I've found some solutions online for Excel, but so far I haven't been able to find anything in smartsheet that will work.

 

Thanks!

Chase

Chris McKay

Hi Chase,

A SUMIFS will do the job. I'm not 100% certain what totals you are trying to pull (e.g. a dollar value somewhere), but this might get you started:

=SUMIFS([Whatever you're trying to SUM]:[Whatever you're trying to SUM], Status:Status, "Completed", [End Date]:[End Date], MONTH(@cell) = 1)

This is setup for January (as per your example, but you could change the MONTH(@cell) = 1 to MONTH(@cell) = MONTH([Some Date]1) so you can configure the report date dynamically.

Hope this helps.

Kind regards,

Chris McKay 

Chase Arthur

Thanks! That didn't seem to work for me. I'm trying to add up the number of items by status and date. So for the example above, there are 2 completed items in January and 1 open item in January. I'd like to be able to have it count the completed items and deliver back the total, which in this case would be 2.

We have all projects in one smartsheet, what I'm really trying to do is build a Dashboard that says something like:

January 2018:

Complete: 45

In Process: 22

Canceled: 1

On Hold: 2

And then be able to repeat this information for every month/year. So I'm trying to build a formula into the sheet for each year/month/status so that my dashboard can pull this information in.

 

Chris McKay

OK, you're just looking for COUNTS. The tricky part here is that COUNTIF/COUNTIFS do not work well with dates, so we need to cheat a little. Try these as a start and adapt as necessary:

Total January 2018

=COUNT(COLLECT([Project Name]:[Project Name], [Finish Date]:[Finish Date], MONTH(@cell) = MONTH(DATE(18, 1, 1))))

Complete January 2018

=COUNT(COLLECT([Project Name]:[Project Name], [Status]:[Status], "Complete", [Finish Date]:[Finish Date], MONTH(@cell) = MONTH(DATE(18, 1, 1))))

In Process April 2018

=COUNT(COLLECT([Project Name]:[Project Name], [Status]:[Status], "In Process", [Finish Date]:[Finish Date], MONTH(@cell) = MONTH(DATE(18, 4, 1))))

You could replace MONTH(@cell) = MONTH(DATE(YY, MM, DD) with a cell reference if you want to use a date column to set the month you're reporting on:

In Process April 2018 based on date in cell

=COUNT(COLLECT([Project Name]:[Project Name], [Status]:[Status], "In Process", [Finish Date]:[Finish Date], MONTH(@cell) = MONTH([Report Date])))

Hope this helps.

Kind regards,

Chris McKay

Chase Arthur

Thank you, Chris!

Chris McKay

My pleasure Chase. Thanks for checking back in.