Sumproduct - counting dates in a datetimelist

[mj2112]

i'm having an issue trying to count the number items in a list occurring on a given date.

=SUMPRODUCT(--(List!$S:$S=$A$1))

where column S is a datetime list and $A$1 is the date i am looking for. i only get 0, i think it is an issue counting a date in a datetime list, but i'm not sure?

thanks

 

[Kevin@Radstock]

Hi mj2112


Try adding the month function to your range $S:$S

=SUMPRODUCT(--(List!MONTH($S:$S)=MONTH($A$1)))

 

[mj2112]

i am getting a generic error, Excel is highlighting "List"; "We found an error with this formula?"

 

[Debraj]

Hi Mj2112!


Try Below..

=SUMPRODUCT(--(INT(list!$S:$S)=$A$1))

Regards,

Deb

 

[mj2112]

Thanks for the help; i fixed it by modifying to =SUMPRODUCT(--(MONTH(List!$S:$S)=MONTH($A$1)))

Thanks for all of your help!

 

[Kevin@Radstock]

HaHa typo, put the month in the incorrect position. At least you have the savvy to change it.