COUNTIF in one colum then COUNTIF in another...???

(Excel 97/2000)

Hi

I am trying to sort out a spreadsheet which goes like this:

A B C D E F
1 PO Agent Area Date
2 0000 JP Kent 01/01/05
3 0001 AM Surrey 01/01/05

....and so on.

What I want to do is find out how many enteries there are of a particular
agent and between certain dates? I.e. How many times JP appears in column "B"
between 2 dates in column "D".

So far i am using COUNTIF statement to to return how many entries there are
between the dates:

(=COUNTIF(A1:A2500,">="&'CELL')*AND(COUNTIFA1:A2500,">="&'CELL'))

This appears to be working quite happily, even if the < and > seems a bit
strange.

BUT...!!!!! I thought i could add an IF statement in front of this and it
would work in the order i want but i am going round in circles now with this.
I am not totally sure excel can perform what i am asking but any input would
be greatly welcomed.

I am also using an embedded combo box which returns the agent name into
another cell for which i calculate on and lots of other calculations all over
the place if this is any use...

Thanks

John

One way:

=SUMPRODUCT(--(B2:B2500=E1),--(D2:D2500>=F1),--
(D2:D2500<=G1))

where E1 = agent, F1 = start date, and G1 = end date.

HTH
Jason
Atlanta, GA

>-----Original Message-----
>(Excel 97/2000)
>
>Hi
>
>I am trying to sort out a spreadsheet which goes like
this:
>
> A B C D
E F
>1 PO Agent Area Date
>2 0000 JP Kent 01/01/05
>3 0001 AM Surrey 01/01/05
>
>....and so on.
>
>What I want to do is find out how many enteries there
are of a particular
>agent and between certain dates? I.e. How many times JP
appears in column "B"
>between 2 dates in column "D".
>
>So far i am using COUNTIF statement to to return how
many entries there are
>between the dates:
>
>(=COUNTIF(A1:A2500,">="&'CELL')*AND
(COUNTIFA1:A2500,">="&'CELL'))
>
>This appears to be working quite happily, even if the <
and > seems a bit
>strange.
>
>BUT...!!!!! I thought i could add an IF statement in
front of this and it
>would work in the order i want but i am going round in
circles now with this.
>I am not totally sure excel can perform what i am asking
but any input would
>be greatly welcomed.
>
>I am also using an embedded combo box which returns the
agent name into
>another cell for which i calculate on and lots of other
calculations all over
>the place if this is any use...
>
>Thanks
>
>John
>.
>

WOW - Thank you so much for this. I have just been reading about 'SUMPRODUCT'
and thinking it might help but didn't know where to start with it.

At the moment your way works and I will break it down to understand it
better now. Thanks again!!!

John

"Jason Morin" wrote:

> One way:
>
> =SUMPRODUCT(--(B2:B2500=E1),--(D2:D2500>=F1),--
> (D2:D2500<=G1))
>
> where E1 = agent, F1 = start date, and G1 = end date.
>
> HTH
> Jason
> Atlanta, GA
>
> >-----Original Message-----
> >(Excel 97/2000)
> >
> >Hi
> >
> >I am trying to sort out a spreadsheet which goes like
> this:
> >
> > A B C D
> E F
> >1 PO Agent Area Date
> >2 0000 JP Kent 01/01/05
> >3 0001 AM Surrey 01/01/05
> >
> >....and so on.
> >
> >What I want to do is find out how many enteries there
> are of a particular
> >agent and between certain dates? I.e. How many times JP
> appears in column "B"
> >between 2 dates in column "D".
> >
> >So far i am using COUNTIF statement to to return how
> many entries there are
> >between the dates:
> >
> >(=COUNTIF(A1:A2500,">="&'CELL')*AND
> (COUNTIFA1:A2500,">="&'CELL'))
> >
> >This appears to be working quite happily, even if the <
> and > seems a bit
> >strange.
> >
> >BUT...!!!!! I thought i could add an IF statement in
> front of this and it
> >would work in the order i want but i am going round in
> circles now with this.
> >I am not totally sure excel can perform what i am asking
> but any input would
> >be greatly welcomed.
> >
> >I am also using an embedded combo box which returns the
> agent name into
> >another cell for which i calculate on and lots of other
> calculations all over
> >the place if this is any use...
> >
> >Thanks
> >
> >John
> >.
> >
>

You can learn more about SUMPRODUCT here:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

HTH
Jason
Atlanta, GA

>-----Original Message-----
>WOW - Thank you so much for this. I have just been
reading about 'SUMPRODUCT'
>and thinking it might help but didn't know where to
start with it.
>
>At the moment your way works and I will break it down to
understand it
>better now. Thanks again!!!
>
>John
>
>"Jason Morin" wrote:
>
>> One way:
>>
>> =SUMPRODUCT(--(B2:B2500=E1),--(D2:D2500>=F1),--
>> (D2:D2500<=G1))
>>
>> where E1 = agent, F1 = start date, and G1 = end date.
>>
>> HTH
>> Jason
>> Atlanta, GA
>>
>> >-----Original Message-----
>> >(Excel 97/2000)
>> >
>> >Hi
>> >
>> >I am trying to sort out a spreadsheet which goes like
>> this:
>> >
>> > A B C D
>> E F
>> >1 PO Agent Area Date
>> >2 0000 JP Kent 01/01/05
>> >3 0001 AM Surrey 01/01/05
>> >
>> >....and so on.
>> >
>> >What I want to do is find out how many enteries there
>> are of a particular
>> >agent and between certain dates? I.e. How many times
JP
>> appears in column "B"
>> >between 2 dates in column "D".
>> >
>> >So far i am using COUNTIF statement to to return how
>> many entries there are
>> >between the dates:
>> >
>> >(=COUNTIF(A1:A2500,">="&'CELL')*AND
>> (COUNTIFA1:A2500,">="&'CELL'))
>> >
>> >This appears to be working quite happily, even if the
<
>> and > seems a bit
>> >strange.
>> >
>> >BUT...!!!!! I thought i could add an IF statement in
>> front of this and it
>> >would work in the order i want but i am going round
in
>> circles now with this.
>> >I am not totally sure excel can perform what i am
asking
>> but any input would
>> >be greatly welcomed.
>> >
>> >I am also using an embedded combo box which returns
the
>> agent name into
>> >another cell for which i calculate on and lots of
other
>> calculations all over
>> >the place if this is any use...
>> >
>> >Thanks
>> >
>> >John
>> >.
>> >
>>
>.
>