+ Reply to Thread
Results 1 to 5 of 5

Formula to distinguish Month Year from prior Month Years

  1. #1
    Registered User
    Join Date
    04-27-2006
    Posts
    26

    Formula to distinguish Month Year from prior Month Years

    This is for a report and on "Summary Worksheet" I want to post "Current Payment" totals IF the invoices from "Tab 3" equal the "month" in G6.

    Say the report is for January - if there are invoices on Tab 3 -worksheet with a January date I want to post all invoice amounts on Summary worksheet under current payment.

    Thank you very much for all help. I hope I'm not too confusing!

    The problem I have run into is I'm picking up invoices for Jan. 2009 instead of Jan 2010.
    Attached Files Attached Files
    Last edited by tmac; 02-16-2010 at 01:14 PM.

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Formula to distinguish Month Year from prior Month Years

    Hi,

    you need to include a year comparison in the sumproduct, like

    =SUMPRODUCT(--(year('Tab 3'!B16:B46)=year($G$6)),--(MONTH('Tab 3'!B16:B46)=MONTH($G$6)),'Tab 3'!F16:F46)

    hth

  3. #3
    Valued Forum Contributor ratcat's Avatar
    Join Date
    03-07-2008
    Location
    Rural NSW, Australia
    MS-Off Ver
    Vista 2007
    Posts
    1,111

    Re: Formula to distinguish Month Year from prior Month Years

    For future reference if you wish to report within a certain date range use this.

    My example is from the 1st Jan to 31st Jan

    Please Login or Register  to view this content.
    Have I made you happy ??? If yes, please make me happy by pressing the http://www.excelforum.com/images/buttons/reputation-40b.png Add Reputation button in my post.
    Please don't forget to do the same to other contributors of this forum.

    Thanks
    I don't void confusion, I create it

  4. #4
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Formula to distinguish Month Year from prior Month Years

    ratcat, you'd have to adjust the formula for every month, though. With the Year() and Month() combo, you won't.

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Formula to distinguish Month Year from prior Month Years

    Quote Originally Posted by teylyn View Post
    Hi,

    you need to include a year comparison in the sumproduct, like

    =SUMPRODUCT(--(year('Tab 3'!B16:B46)=year($G$6)),--(MONTH('Tab 3'!B16:B46)=MONTH($G$6)),'Tab 3'!F16:F46)

    hth
    I would add one point regards the above approach and that is that both MONTH & YEAR functions will explicitly coerce the underlying values.
    It follows that should (for whatever reason) any of the referenced cells (B16:B46) contain non-numerics the above would generate #VALUE! errors.

    You could instead conduct a Year/Month test in "one go" and avoid numeric coercion by doing the opposite - ie using a Text based coercion, eg:

    Please Login or Register  to view this content.

    The above will not be affected by data type in the source range (other than underlying errors of course).

    However, the TEXT coercion approach is not overly efficient - Bob Phillips has argued in the past that the above is probably slower then the separate Year and Month tests combined - but obviously less prone to error.

    If we were to accept that

    a) there is no risk of non-numeric values

    b) we're only conducting single month tests
    then I would say that rather than use MONTH & YEAR you could instead use:

    Please Login or Register  to view this content.

    as again this limits the amount of cells being iterated (ie single test) and does not use slow Text conversion... like MONTH/YEAR it is open to error with underlying non-numerics given explicit coercion resulting from use of DAY.

    Ratcat's suggested approach of two tests is of course the most flexible of all given that it allows for any greater than or less than combination of dates (the date values themselves can of couse be cell references).
    Last edited by DonkeyOte; 02-14-2010 at 08:04 AM. Reason: typo

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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