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
|