# Formula to distinguish Month Year from prior Month Years

1. ## 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.

2. ## 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. ## 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.``

4. ## 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. ## Re: Formula to distinguish Month Year from prior Month Years

Originally Posted by teylyn
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).

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

#### 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