Remember Me?

#1
February 22nd 05, 11:49 AM
 JonnieP Posts: n/a
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:A250 0,"="&'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

#2
February 22nd 05, 12:32 PM
 Jason Morin Posts: n/a

One way:

=SUMPRODUCT(--(B2:B2500=E1),--(D22500=F1),--
(D22500<=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
.

#3
February 22nd 05, 02:23 PM
 JonnieP Posts: n/a

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),--(D22500=F1),--
(D22500<=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
.

#4
February 22nd 05, 02:55 PM
 Jason Morin Posts: n/a

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

and thinking it might help but didn't know where to

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),--(D22500=F1),--
(D22500<=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

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
.

.

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Russell Hampton Excel Worksheet Functions 6 December 18th 04 07:39 PM maswinney Excel Worksheet Functions 3 November 15th 04 11:06 PM Liz G Excel Worksheet Functions 2 November 1st 04 11:20 PM Liz G Excel Worksheet Functions 3 November 1st 04 09:34 PM Boggled Excel User Excel Worksheet Functions 14 October 29th 04 06:38 PM

All times are GMT +1. The time now is 09:50 AM.