SUMPRODUCT to calculate # of entries in a date range

[virtuosok]

Hi,
I have the following 2 formulas which work well for my purpose:

=SUMPRODUCT((A4:A200="USA")*(G4:T200<DATE(2021,11,1)))

(this one returns 51)

=SUMPRODUCT((A4:A200="USA")*(G4:T200>DATE(2021,9,30)))

(this one returns 650)
...however, when I stitch them together to see how many hits for USA in Oct 21, the formula is not working as it should and it returns 650 (the result should be in low double digits)

=SUMPRODUCT((A4:A200="USA")*(G4:T200>DATE(2021,9,30)*(G4:T200<DATE(2021,11,1))))

What am I doing wrong please?

 

[Marcelo Branco]

Parentheses in the wrong place

Try
=SUMPRODUCT((A4:A200="USA")*(G4:T200>DATE(2021,9,30))*(G4:T200<DATE(2021,11,1)))

M.

 

[virtuosok]

Wow indeed, thanks a lot
Is there a way to embed a MONTH and YEAR function instead to make the formula shorter? i.e.
(--(MONTH($G$4:$T$300),0)=10)
(--(YEAR($G$4:$T$300),0)=2021)

 

[Marcelo Branco]

Is there a way to embed a MONTH function instead to make the formula shorter?

=SUMPRODUCT((A4:A200="USA")*(MONTH(G4:T200)=10)*(YEAR(G4:T200)=2021))
not much shorter

M.