| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCTS
I have been working on a spreadsheet where I need to count the number of
occurrences dependent on two criteria. Here is my situation time blocks 0:22 2 0:15 1 0:35 1 I need to sum the number of times that a time of greater than 20 min time is recored for a single (1) block. In my above example I should get 2. I worked w/ countif and sumproducts but I am going wrong somewhere. Any suggestions? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCTS
On Mon, 10 Jul 2006 10:39:01 -0700, AJP
wrote: I have been working on a spreadsheet where I need to count the number of occurrences dependent on two criteria. Here is my situation time blocks 0:22 2 0:15 1 0:35 1 I need to sum the number of times that a time of greater than 20 min time is recored for a single (1) block. In my above example I should get 2. I worked w/ countif and sumproducts but I am going wrong somewhere. Any suggestions? Are you sure the answer you're expecting given the above data isn't 1? i.e. the single block with a time of 0:35 Rgds __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCTS
hi,
try =sumproduct(--(c1:c100=time(0,22,0))*(d6:d100=1)*(d6:d100)) hth regards from Brazil Marcelo "AJP" escreveu: I have been working on a spreadsheet where I need to count the number of occurrences dependent on two criteria. Here is my situation time blocks 0:22 2 0:15 1 0:35 1 I need to sum the number of times that a time of greater than 20 min time is recored for a single (1) block. In my above example I should get 2. I worked w/ countif and sumproducts but I am going wrong somewhere. Any suggestions? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCTS
I believe you're right on that. Sorry for the mix up.
"Richard Buttrey" wrote: On Mon, 10 Jul 2006 10:39:01 -0700, AJP wrote: I have been working on a spreadsheet where I need to count the number of occurrences dependent on two criteria. Here is my situation time blocks 0:22 2 0:15 1 0:35 1 I need to sum the number of times that a time of greater than 20 min time is recored for a single (1) block. In my above example I should get 2. I worked w/ countif and sumproducts but I am going wrong somewhere. Any suggestions? Are you sure the answer you're expecting given the above data isn't 1? i.e. the single block with a time of 0:35 Rgds __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCTS
I'll try it. Thanks for the help.
"Marcelo" wrote: hi, try =sumproduct(--(c1:c100=time(0,22,0))*(d6:d100=1)*(d6:d100)) hth regards from Brazil Marcelo "AJP" escreveu: I have been working on a spreadsheet where I need to count the number of occurrences dependent on two criteria. Here is my situation time blocks 0:22 2 0:15 1 0:35 1 I need to sum the number of times that a time of greater than 20 min time is recored for a single (1) block. In my above example I should get 2. I worked w/ countif and sumproducts but I am going wrong somewhere. Any suggestions? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sumproducts, Counta Lookup Ref Formulas | Excel Discussion (Misc queries) | |||
| Lookups and References in relations to countA or sumproducts | Excel Worksheet Functions | |||
| Need know what are valid SUMPRODUCTS arguments and functions | Excel Worksheet Functions | |||
| large sumproducts causing memory errors | Excel Discussion (Misc queries) | |||
Linear Mode
