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 |
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 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),--(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 . . |
All times are GMT +1. The time now is 03:17 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com