SUMPRODUCTS

[AJP]

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?

[Richard Buttrey]

On Mon, 10 Jul 2006 10:39:01 -0700, AJP
<[email protected]> 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
__________________________

[Marcelo]

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?

[AJP]

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
> <[email protected]> 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
> __________________________
>

[AJP]

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?