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

Find more posts tagged with

Sales

Sheets

Hospitality

Finance

Calendar View

Formulas

Reports

Dashboards

Project Management

Cell linking

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…