Re: SUMPRODUCT Formula works, include date format

What you are not explaining is HOW it doesn't work. What results are you expecting, and what are you seeing that are wrong?

My guess would be that you are seeing incorrect results where the date by itself would put the item into one category, but the addition of the time throws it into a different category.

What you need to bear in mind is that dates and times are simply numbers. One day equals one whole number, and times are represented by fractions of the number 1 (e.g. 01:00 1:00am = 1 divided by 24). Today's date, 6 January 2016, is represented by the number 42375. Midday on Jan 6 would be represented by 42375.5.

Your formula is taking the contents of cell I4635 and deducting 30, presumably to find items that are 30 days old. Including or excluding the time in those calculations is therefore in some instances going to impact on the result.

If you want to ignore the time in your calculations, based on Carim's suggestion, you can use something like

=SUMPRODUCT(--($E$2:E4635="NPR"),--(INT($N$2:N4635)=INT(N4635)),--(INT($I$2:I4635)<=INT(I4635)),--(INT($I$2:I4635)>=INT(I4635)-30))

INT would just remove the decimal portion of the number.