Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 22nd 05, 11:49 AM
JonnieP
 
Posts: n/a
Default 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   Report Post  
Old February 22nd 05, 12:32 PM
Jason Morin
 
Posts: n/a
Default

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   Report Post  
Old February 22nd 05, 02:23 PM
JonnieP
 
Posts: n/a
Default

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   Report Post  
Old February 22nd 05, 02:55 PM
Jason Morin
 
Posts: n/a
Default

You can learn more about SUMPRODUCT he

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


.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
countif statement Russell Hampton Excel Worksheet Functions 6 December 18th 04 07:39 PM
Countif - Countif maswinney Excel Worksheet Functions 3 November 15th 04 11:06 PM
countif, again Liz G Excel Worksheet Functions 2 November 1st 04 11:20 PM
combining countif formulas Liz G Excel Worksheet Functions 3 November 1st 04 09:34 PM
lookup in colum a and compare values in colum b Boggled Excel User Excel Worksheet Functions 14 October 29th 04 06:38 PM


All times are GMT +1. The time now is 11:29 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017