How can I show Year to Date orders from previous year quickly?

**CenterShock** · 03-02-2015, 03:37 PM

Hi gang.

I keep a spreadsheet of order history and I have wanted to add a feature to the file that I sure is possible but I am not sure how to go about it.

Attached is a sample of my spreadsheet. There are multiple tabs. (for each year of orders)

What I would like to do is create a formula that would total the dollar amount from Jan 1 to the present date but from the prior year.

Example: Today is March 2nd. I would like the calculation to go to 2014 tab and calculate orders from Jan 1 to March 2 of 2014 and display it in a cell on my current 2015 tab. (see note on sample file)
When I open the same file next week on the 9th the cell would display the totals from Jan 1 to March 9 in that cell. Etc. Etc.

Oh, yeah. I am working in Excel 2010 now. I need to go into my profile and update that!

Thanks in advance for your time!

**Zodeeak** · 03-02-2015, 04:23 PM

Try the attached.

Hope this helps.

-Z

**FDibbins** · 03-02-2015, 04:39 PM

You dont actually have any 2014 data in that table, but this will give you what you want...
=SUMIFS($D$1:$D$317,$E$1:$E$317,">="&DATE(YEAR(TODAY())-1,1,1),$E$1:$E$317,"<="&EDATE(TODAY(),-12))

Not sure if I used the right columns, so adjust as needed

On a side note, I suggest you put that summary table (rows 322:328) on another sheet or at the top, off to the side. Where it is now, will make growing your table a problem, and also, you wont be able to use full-column ranges in formulas

**skywriter** · 03-02-2015, 04:42 PM

If you are going by the order date. The D column of the 2014 sheet, then try this.

Formula:

Please Login or Register  to view this content.

**CenterShock** · 03-02-2015, 05:13 PM

Thanks for the quick replies guys! I am working on applying the code from Zodeeak's file right now. I got it to work with 2014 and am not trying to get 2013 to work.
Yes, I am working from the ORDER DATE so that looks to be the one thing that I am tweaking so that the calculation is pulling from the order date and not the ship date.

Thanks for the suggestion regarding the summary table FDibbins. I actually draw my data from a Storefront and I open the data I download in Excel and massage some of the info that I get and then I copy the rows from that file and "Insert" into the spreadsheet that I keep so that summary table keeps moving to the end of the spreadsheet. I may move it some day but it is working for me the way it is currently.

**skywriter** · 03-02-2015, 05:26 PM

Thanks for the rep points. Note my formula started on row 3 because you had 1 date from 2013. I was a little lazy and didn't want to write a formula that filtered out days before 1/1/2014, so be careful if you are using mine for other sheets.

How can I show Year to Date orders from previous year quickly?

LinkBack

Thread Tools

Rate This Thread

Display

How can I show Year to Date orders from previous year quickly?

Re: How can I show Year to Date orders from previous year quickly?

Re: How can I show Year to Date orders from previous year quickly?

Re: How can I show Year to Date orders from previous year quickly?

Re: How can I show Year to Date orders from previous year quickly?

Re: How can I show Year to Date orders from previous year quickly?

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Current year and month and Previous and year

Formula to compare previous year to current year

Keep month and date but change year to current year and reoccur when new year rolls over.

[SOLVED] Keeping a date as current year, even though formula determines previous year

Determine if date is before end of fiscal year of previous year

Bookmarks

Bookmarks

Posting Permissions