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

SUMIF function not working
=SUMIFS({GM Compliance Trained}, {GM Compliance Training Dept}, Department@row , {Compliance Training Date}, MONTH(7, 8, 9)) GM Compliance Trained is the number of employees trained. I am trying to sum the number of employees trained in each department during 3rd quarter (July 1-Sep 30). Thinking I need an IFERROR in there…
Prevent due dates from falling within the same calendar week?
Hi. I am working on a (roughly) annual document review system, in partnership with another team in my organization. They have requested that they receive one document to review per week. But there are more documents to review than there are weeks in the year. So I need to figure out a method for ensuring that I don't send…
How to record date without time
I would like to use automation on a sheet to record the Open and Closed date for a given RAID item. I don't want to record the time, however. Is there a way to exclude a time stamp from the automation? If it is not possible to exclude the time stamp, what formula would I need to use to extract the datae and time into…