Counting Financials within Past 365 Days (Year to Date)

Hi,

I have a Smartsheet that counts financial errors by individual. I want to change this so it only counts figures Year to Date. I'm therefore trying to manipulate my formula to only count within the past 365 Days. To make it more complicated, I'm referencing from a separate sheet. I've tried (TODAY) - 365 but unsure if i'm using the formula in the correct place.

As I'm referencing from a separate sheet the formula isn't clear so I hope the screenshots are clear.

I need my formula to read the 'Amount' Column and total this down the Smartsheet. I'm using a SUMIF formula as I need it to count by 'TP Agent' where I use the initials of staff to total errors per agent. I want to extend the formula so it only picks up transactions that have taken place within the past 365 Days. The formula on my sheet looks like the below:

=SUMIFS({ADM Slave Sheet Range 1}, {ADM Slave Sheet Range 2}, "AG"

Sheet Range 1 is the 'Amount', Sheet Range 2 is the 'TP Agent' I just need to know how to tag onto the end of the formula to only read dates within the past year.

Many Thanks

Matt

Find more posts tagged with

Formulas

Accepted answers

All comments

Summer

@Matt Wood

I am a fan of helper columns, especially if they are providing something like a date calculation. Having the calculation in it's own cell (on sheet, or in the sheet summary) allows me to know the reference point at a glance.

My suggested approach implements a helper column for your Today value.

Make sure your column properties (or cell properties if using sheet summary) are Date and put in this calculation =TODAY() - 365 like you were originally using.

Make sure on Sheet 1, your column with the dates is also set as a Date column.

Back on your sheet 2 where you're collecting the data use a formula like this in the field you want the amounts in. For my example the formula is in "Amount this past year"

=SUMIFS({Sheet 1 | Amount}, {New Sheet | TP Agent}, =[TP AGENT]@row, {Sheet 1 | Date}, >=$[365 Days Ago]$1)

I hope this helps. Happy to break things out further as needed.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.