Sumproduct vs countif

[Scott Kieta]

I am trying to count the # of times a value occurs in a column, the column
format is dd/mm/yyyy hh:mm AM/PM and i want to count all occurrences between
7am and 8am, 8-9, 9-10, etc and have utilized this formula

=SUMPRODUCT((HOUR($I$15:$I$350)>=7)*(HOUR($I$15:$I$350)<8))

and it seems to work, however with the midnight to 1 am

=SUMPRODUCT((HOUR($I$15:$I$350)>=0)*(HOUR($I$15:$I$350)<1))

it is returning the blank cells as well how can i adjust this to not count
the blank cells, also is there an easier countif formula that could do the
same thing?
Thank You

 

[Gary''s Student]

=SUMPRODUCT((HOUR($I$15:$I$350)>0)*(HOUR($I$15:$I$350)<1))

 

[Lars-Åke Aspelin]

I am trying to count the # of times a value occurs in a column, the column
format is dd/mm/yyyy hh:mm AM/PM and i want to count all occurrences between
7am and 8am, 8-9, 9-10, etc and have utilized this formula

=SUMPRODUCT((HOUR($I$15:$I$350)>=7)*(HOUR($I$15:$I$350)<8))

and it seems to work, however with the midnight to 1 am

=SUMPRODUCT((HOUR($I$15:$I$350)>=0)*(HOUR($I$15:$I$350)<1))

it is returning the blank cells as well how can i adjust this to not count
the blank cells, also is there an easier countif formula that could do the
same thing?
Thank You

If you don't want to include the blank cells you can add an extra
multiplication, like this:

=SUMPRODUCT((HOUR($I$15:$I$350)>=0)*(HOUR($I$15:$I$350)<1)*(NOT(ISBLANK($I$15:$I$350))))

Hope this helps / Lars-Åke

 

[Lars-Åke Aspelin]

If you don't want to include the blank cells you can add an extra
multiplication, like this:

=SUMPRODUCT((HOUR($I$15:$I$350)>=0)*(HOUR($I$15:$I$350)<1)*(NOT(ISBLANK($I$15:$I$350))))

Hope this helps / Lars-Åke

And you dont need both >=0 and <1. Only =0 is needed, like this:

=SUMPRODUCT((HOUR($I$15:$I$350)=0)*(NOT(ISBLANK($I$15:$I$350))))

Lars-Åke

 

[ShaneDevenshire]

Hi Scott,

In addition you could use the shorter version

=countif(HOUR($I$15:$I$350)<1)


I'm assuming you don't have negative times.

 

[Scott Kieta]

Thank you, worked like a charm

And you dont need both >=0 and <1. Only =0 is needed, like this:

=SUMPRODUCT((HOUR($I$15:$I$350)=0)*(NOT(ISBLANK($I$15:$I$350))))

Lars-Ã…ke