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
.
.