Count number of rows based on date

[BrutalLogiC]

Hi I was given a spreadsheet with the formula below which is counting the number of entries in a given month:
{=SUM(IF(MONTH(Sheet1!$V$1195:$V$1481)=MONTH(E4),IF(YEAR(Sheet1!$V$1195:$V$1481)=YEAR(E4),1,0),0))}

column V has dates entered like dd/mm/yyyy
E4 = 01/01/2021
E5 = 01/02/2021
etc

is there a better way of doing this formula so it's not an array....

 

[jasonb75]

Perhaps

=COUNTIFS(Sheet1!$V$1195:$V$1481,">"&EOMONTH(E4,-1),Sheet1!$V$1195:$V$1481,"<="&EOMONTH(E4,0))

The first EOMONTH function is not strictly necessary but it will allow the formula to work correctly if the dates in E4, etc are anything other than the 1st (the same as your original formula would).

 

[BrutalLogiC]

Perhaps

=COUNTIFS(Sheet1!$V$1195:$V$1481,">"&EOMONTH(E4,-1),Sheet1!$V$1195:$V$1481,"<="&EOMONTH(E4,0))

The first EOMONTH function is not strictly necessary but it will allow the formula to work correctly if the dates in E4, etc are anything other than the 1st (the same as your original formula would).

Thanks very much.