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?

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
__________________________

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?

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
__________________________

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?