=SUMPRODUCT Current Month Condition Help

[Aimee S.]

This currently works for one of my needs which is to say it counts if there is a value of 1 in column L, the word "Base" in column C, and the date present in column R falls between 3/1/11 and 3/31/11:

=SUMPRODUCT(--(L3:L5000=1),--(C3:C5000="Base"),--(R3:R5000>=DATE(2011,3,1)),--(R3:R5000<=DATE(2011,3,31)))

The second and more pressing of my needs is to have this same formula, but have these three conditions for SUMPRODUCT to count:

L3:L5000=1
C3:C5000="Base"

and the third condition, which is the one I am stuck on, should show R3:R5000 must fall within the current month, whatever that may be as time goes on.
Meaning, greater than or equal to the current month's 1st day, and less than or equal to the current month's last day.



Please help!

 

[tushiroda]

try

=SUMPRODUCT(--(L3:L5000=1),--(C3:C5000="Base"),--(R3:R5000=MONTH(TODAY())))

 

[T. Valko]

This currently works for one of my needs which is to say it counts if there is a value of 1 in column L, the word "Base" in column C, and the date present in column R falls between 3/1/11 and 3/31/11:

=SUMPRODUCT(--(L3:L5000=1),--(C3:C5000="Base"),--(R3:R5000>=DATE(2011,3,1)),--(R3:R5000<=DATE(2011,3,31)))

The second and more pressing of my needs is to have this same formula, but have these three conditions for SUMPRODUCT to count:

L3:L5000=1
C3:C5000="Base"

and the third condition, which is the one I am stuck on, should show R3:R5000 must fall within the current month, whatever that may be as time goes on.
Meaning, greater than or equal to the current month's 1st day, and less than or equal to the current month's last day.



Please help!

Assuming that you want the current month AND the current year.

One way...

=SUMPRODUCT(--(L3:L5000=1),--(C3:C5000="Base"),--(TEXT(R3:R5000,"mmmyyyy")=TEXT(NOW(),"mmmyyyy")))

 

[Norie]

Replace the hard-coded month in the formula with MONTH(TODAY()).

 

[Aimee S.]

Awesome thanks!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

 

[T. Valko]

Awesome thanks!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

You're welcome!