SUMPRODUCT and MONTH/YEAR help

[ddub25]

=SUMPRODUCT(--('Rec. Shared'!$M$12:$M$1465=$I$1),--(MONTH('Rec. Shared'!$Y$12:$Y$1465)=MONTH(A17)),'Rec. Shared'!$U$12:$U$1465)

Need help to make the formula above more accurate. This formula reads the text in $I$1 to find MATCHES in $M$12:$M$1465. It also reads the month in A17 (Sep-11, in this case) and finds MATCHES in $Y$12:$Y$1465. It then totals values in $U$12:$U$1465 where both these conditions are met.

This formula is only totalling values from Sep-10 and completely disregarding Sep-11. I assume it also needs to search for the YEAR somehow. Can anyone help?

Dan

 

[p45cal]

This is unexpected because I'd expect totals from all years to be included since there's no year ref at all in the formula!
Just to be sure, you refer to dates as Sep-!0, Sep-11, this means Sep-2011 ans Sep-2010, yes?

If you want to restrict to just one year then you need to add another bit within the SumProduct parentheses like:
--(YEAR('Rec. Shared'!$Y$12:$Y$1465)=YEAR(A17))

Otherwise, if you could post a version of your sheet on the interweb somewhere (eg.box.net)…

 

[Aladin Akyurek]

=SUMPRODUCT(--('Rec. Shared'!$M$12:$M$1465=$I$1),--(MONTH('Rec. Shared'!$Y$12:$Y$1465)=MONTH(A17)),'Rec. Shared'!$U$12:$U$1465)

Need help to make the formula above more accurate. This formula reads the text in $I$1 to find MATCHES in $M$12:$M$1465. It also reads the month in A17 (Sep-11, in this case) and finds MATCHES in $Y$12:$Y$1465. It then totals values in $U$12:$U$1465 where both these conditions are met.

This formula is only totalling values from Sep-10 and completely disregarding Sep-11. I assume it also needs to search for the YEAR somehow. Can anyone help?

Dan

If A17 houses a true first day date of the month/year of interest like 1-Sep-2011, possibly formatted to display Sep-11, you can also invoke:

=SUMPRODUCT(
--('Rec. Shared'!$M$12:$M$1465=$I$1),
--('Rec. Shared'!$Y$12:$Y$1465-DAY('Rec. Shared'!$Y$12:$Y$1465)+1=A17),
'Rec. Shared'!$U$12:$U$1465)

 

[jim may]

I've been successful wrapping things with the TEXT() function:

--(TEXT(MONTH('Rec. Shared'!$Y$12:$Y$1465,"mmm")=TEXT(A17,"mmm"))

 

[Aladin Akyurek]

I've been successful wrapping things with the TEXT() function:

--(TEXT(MONTH('Rec. Shared'!$Y$12:$Y$1465,"mmm")=TEXT(A17,"mmm"))

The OP wants to cover both month and year. If you want to make use of TEXT, something like the following would be needed:

--(TEXT('Rec. Shared'!$Y$12:$Y$1465,"mmmyy")=TEXT(A17,"mmmyy"))

 

[ddub25]

Yes, Sep-11 (in 'mmm-yy' format) means Sep-2011. I am surprised that it only totalled values from Sep-10 and not Sep-11 also.

My months for the A Column (A17) are in 'mmm-yy' format displaying Sep-11, but showing 01/09/2011 in the formula bar as you thought, Aladin, so I've used your formula which does exactly what I'm after.

Thanks P45cal, Jim and Aladin for your help, it's been puzzling me for a while now, but problem solved. Thanks again.

Dan

 

[jim may]

The OP wants to cover both month and year. If you want to make use of TEXT, something like the following would be needed:

--(TEXT('Rec. Shared'!$Y$12:$Y$1465,"mmmyy")=TEXT(A17,"mmmyy"))

Aladin, thanks My post should not have include the Month() portion, thus it should have been:

--(TEXT('Rec. Shared'!$Y$12:$Y$1465,"mmm")=TEXT(A17,"mmm"))

Thinking the Corrected formula would produce ALL Septs (regardless of year),
it would do so.. Correct? Jim

 

[Aladin Akyurek]

Aladin, thanks My post should not have include the Month() portion, thus it should have been:

--(TEXT('Rec. Shared'!$Y$12:$Y$1465,"mmm")=TEXT(A17,"mmm"))

Thinking the Corrected formula would produce ALL Septs (regardless of year),
it would do so.. Correct? Jim

Yes, that's correct. Hence the need for "mmmyy" or "mmm-yy" or the like.

 

[Aladin Akyurek]

Yes, Sep-11 (in 'mmm-yy' format) means Sep-2011. I am surprised that it only totalled values from Sep-10 and not Sep-11 also.

My months for the A Column (A17) are in 'mmm-yy' format displaying Sep-11, but showing 01/09/2011 in the formula bar as you thought, Aladin, so I've used your formula which does exactly what I'm after.

Thanks P45cal, Jim and Aladin for your help, it's been puzzling me for a while now, but problem solved. Thanks again.

Dan

You are welcome. Thanks for providing us feedback.