+ Reply to Thread
Results 1 to 5 of 5

Formula to show change from previous month

  1. #1
    Forum Contributor
    Join Date
    12-21-2004
    Location
    MN
    MS-Off Ver
    Microsoft 365
    Posts
    213

    Formula to show change from previous month

    I am sure there is way to do this, I am just not sure how.

    Date Customer ATM IP Total ATMDif IPDif
    08/01/14 Company 1 250 325 575
    08/01/14 Company 2 132 285 417
    09/01/14 Company 1 249 333 582 (1) 8
    09/01/14 Company 2 130 290 420 (2) 5

    This is a running table from month to month. I want to be able to see the change for each customer for both the ATM count, and the IP count.
    I need a formula that will look at each count, and show the difference from the prior month. So say in August, customer 1 had 250 clients on ATM and in Sept it was 249. The change is shown there as -1, but I would like a formula to automatically do that. So it will need to look at a previous date, for the same customer, and look at both ATM and IP counts.
    Hope that makes sense.
    Last edited by karstens; 09-05-2014 at 04:11 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: Formula to show change from previous month

    In F2 try this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    It should be able to be dragged down and across.

    It assumes all dates are the first of the month. It will return a blank if there was no record the previous month.
    Please add reputation by clicking on the * if I have helped.
    Please mark the thread SOLVED if your issue has been resolved.
    Thanks, Glenn.

  3. #3
    Forum Contributor
    Join Date
    12-21-2004
    Location
    MN
    MS-Off Ver
    Microsoft 365
    Posts
    213

    Re: Formula to show change from previous month

    Thanks for the suggestion!
    I copied and pasted your formula into F2 and I get an error that I have entered too many arguments.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,047

    Re: Formula to show change from previous month

    I thin that should be sumifS()...
    =IFERROR(C2-SUMIFs(C$1:C1,$B$1:$B1,$B2,$A$1:$A1,EDATE($A2,-1)),"")
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: Formula to show change from previous month

    Quote Originally Posted by FDibbins View Post
    I thin that should be sumifS()...
    =IFERROR(C2-SUMIFs(C$1:C1,$B$1:$B1,$B2,$A$1:$A1,EDATE($A2,-1)),"")
    Thanks Ford.

  6. #6
    Forum Contributor
    Join Date
    12-21-2004
    Location
    MN
    MS-Off Ver
    Microsoft 365
    Posts
    213

    Re: Formula to show change from previous month

    Thank you so much to both gak67 and FDibbins! This worked perfectly.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] How do I change the formula to show the previous wednesday day from todays date
    By john dalton in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-08-2014, 08:14 AM
  2. [SOLVED] Formula for amount on previous month based on current month
    By Yu Marquez in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-21-2014, 12:42 AM
  3. Month over Month formula to show increase or decrease in rankings for SEO
    By lindsbay in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-12-2014, 03:03 AM
  4. [SOLVED] Open and show Previous Month
    By tommyfernandez in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-01-2013, 10:59 AM
  5. Replies: 5
    Last Post: 10-04-2012, 07:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1