Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct vs countif
=SUMPRODUCT((HOUR($I$15:$I$350)0)*(HOUR($I$15:$I$ 350)<1))
-- Gary''s Student - gsnu200787 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct or countif or both... | Excel Discussion (Misc queries) | |||
SumProduct and Countif | Excel Worksheet Functions | |||
sumproduct or countif | Excel Discussion (Misc queries) | |||
Countif or Sumproduct | Excel Discussion (Misc queries) | |||
countif, sumproduct | New Users to Excel |