Sumproduct vs countif

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Scott Kieta - 22 May 2008 20:06 GMT

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

Reply to this Message

Gary''s Student - 22 May 2008 20:20 GMT

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

Signature

Gary''s Student - gsnu200787

Reply to this Message

Lars-Åke Aspelin - 22 May 2008 20:41 GMT

>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
[quoted text clipped - 10 lines]
>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

Reply to this Message

Lars-Åke Aspelin - 22 May 2008 20:44 GMT

>>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
[quoted text clipped - 17 lines]
>
>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

Reply to this Message

Scott Kieta - 22 May 2008 21:41 GMT

Thank you, worked like a charm

> >>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
[quoted text clipped - 23 lines]
>
> Lars-Åke

Reply to this Message

ShaneDevenshire - 22 May 2008 20:59 GMT

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.

Signature

Thanks,
Shane Devenshire

> 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
[quoted text clipped - 10 lines]
> same thing?
> Thank You

Reply to this Message