Sumproduct vs countif
 

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

Sumproduct vs countif
 

=SUMPRODUCT((HOUR($I$15:$I$350)0)*(HOUR($I$15:$I$ 350)<1))
--
Gary''s Student - gsnu200787

Sumproduct vs countif
 

On Thu, 22 May 2008 12:06:01 -0700, Scott Kieta
wrote:

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

Sumproduct vs countif
 

On Thu, 22 May 2008 19:41:07 GMT, Lars-Åke Aspelin
wrote:

On Thu, 22 May 2008 12:06:01 -0700, Scott Kieta
wrote:

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


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

Sumproduct vs countif
 

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.
--
Thanks,
Shane Devenshire


"Scott Kieta" wrote:

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

Sumproduct vs countif
 

Thank you, worked like a charm

"Lars-Ã…ke Aspelin" wrote:

On Thu, 22 May 2008 19:41:07 GMT, Lars-Ã…ke Aspelin
wrote:

On Thu, 22 May 2008 12:06:01 -0700, Scott Kieta
wrote:

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


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