• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Comparing sales revenue to last year in the same month

B

blakksheep57

New Member
Hi everyone! I'm having a bit of a challenge trying to create a real time revenue tracker that will allow me to see how our revenue is doing (by percentage) compared to last year on the same day.


My scenario: I'm using a pivot table that can show me my revenue by year,quarter, month, and day. I want to create a dashboard that will allow me to see how we are doing, sales wise, in real-time compared to last year on the exact same day. So today is July 5th, 2012 and I want to excel to compare TOTAL sales from January 1st, 2011 - July 5th, 2012 TO January 1st, 2012 - July 5th, 2012. I also want this capability for quarter and month. I also want excel to automatically track this as each day passes.


What I have so far: I currently have my yearly, quarterly, and monthly revenue being compared to the END of the year, quarter, and month revenue but this tells me very little unless it's the end of the month. I want to be able to know today that I am doing better or worse compared to the same day last year.


What I have tried: So far I've tried using SUM, NESTED IF with SUM, =NOW, =TODAY, vlookup, match in different ways to try to get this thing automated but obviously have failed miserably. I would love it if someone can point me in the right direction. I'm hoping not to have to use a macro or VBA but if that is the only thing then anything would help at this point
 
Blakksheep57


Firstly, Welcome to the Chandoo.org forums.


I'd be looking to use either the Sumproduct() or Sumifs() functions


You will need to retrieve the data >= 1st of the year and <= Relevent date


Sumproduct

=Sumproduct((Date Range>=Date(Year, 1, 1))*(Date Range<=Today())*Data Range)


Sumifs

=Sumifs(Data Range,Date Range,">="&Date(Year, 1, 1),Date Range,"<="&Today())
 
You my friend are a GENIUS! or rather a EXCEL NINJA GENIUS! As you might have already assumed the suggestion you gave worked like a charm! Thank you thank you! And thanks for the warm welcome. I'll definitely be on this site more often.
 
You must log in or register to reply here.
Back
Top