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:
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:
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).
Bookmarks