Using SUMPRODUCT to count entries between the times of 12am-1am only

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

I don't see any issues with your formula. Perhaps the array is not locked. Please try the below one and if the problem still persists, kindly provide your sample workbook.

Formula:

Please Login or Register  to view this content.

It's because G5 and F5 have an underlying date (01/01/1901), so you need to change them to times ONLY. In other words, delete the date part.

Here's the workbook with the fix applied.

Look at the attachment to post #7. Did you also remove the SPACE between the date and the time?

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	A	B
8	4th HOUR - ( 12AM - 1AM )	139

Sheet: 01

try

Formula:

Please Login or Register  to view this content.

there is only one 12:20:21 AM

I think it's the dates in the time values that are messing things up. I can see only ONE value that should count, actually, not two.

This will work if you get rid of the dates in your entries:

=SUMPRODUCT((B14:B184<=INDEX!G5)*(B14:B184>=INDEX!F5)*(B14:B184<>""))

Because in formula, Excel treats blank value as 0. therefore a criteria statement like: (B4:B184<>"") should be add in SUMPRODUCT (as #11 did)

You would be better off cleadning the dates out of the time data and then using this:

=SUMPRODUCT((B14:B184<=INDEX!G5)*(B14:B184>=INDEX!F5)*(B14:B184<>""))

Get the input right in the first place and the rest will look after itself. I've removed dates from the entries in yellow.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

Please see my advice in post #16. The solution you've been given is a workaround - you need to cleanse your data entries.

Please mark this thread as SOLVED, as advised in post #16.