On 15 Mar, 06:21, Harlan Grove wrote:
wrote...

...

So far I have: {=SUM(((Presentations)=VALUE("1 Jan 2009"))*
((Presentations)<=VALUE("31 Jan 2009")))} . . . I
need to add to this to find "who returned" in the next colum taking
into mind that I cannot apply the formula to the whole of the next
colum as the clients date of return does vary. . . .

If Presentations refers to a cell in one column and Returns refers to
the cell one column to the right of the Presentations cell, try

=IF(AND(TEXT(Presentations,"yyyymmm")="2009Jan",CO UNT
(Returns)),Returns,"")

On the other hand, it looks like your original formula is an array
formula, so Presentations would seem to be a multiple cell range. If
so, it's unclear what exactly you want. If you want the first date of
return after Jan 2009 for those with presentations in Jan 2009, try
the array formula

=MIN(IF((TEXT(Presentations,"yyyymmm")="2009Jan")
*(ReturnsDATE(2009,1,31)),Returns))

If you want something else, please describe in greater detail ALL the
data fields involved, what their rough size/shape is (single or
multiple column, single or multiple row), and how the result should be
determined from the data.

Yes - sorry guys it was a little unclear --
the colums are set accordingly:

A B
C D E
01 | Client name |Presentations| Sign Up 1 | Sign Up 2
| Program
02 | Speers Carl | 03 Jan 09 | |
| To track the sales conversation rate on a
monthly basis we can see that there were
03 | Whitee Adam | 03 Jan 09 | 10 Feb 09 | 17 Feb 09 | 25
Mar 09 6 presentations in Jan 09 but only 2 of them
converted to the next stage. Giving a
04 | Lloydal Nicholas | 04 Jan 09 |
| | 33% converstion rate for stage 1 to 2 for
Jan 09. Tracking the same Jan clients
05 | Oslandy Simone | 14 Jan 09 | 19 Jan 09 | 06 Feb 09
| we had a 100% converstion rate from stage
2 to 3 and then 50% from Sign Up 2
06 | Smithers Tracy | 15 Jan 09 |
| | to program.
07 | Free Andrew | 16 Jan 09 | | |
08 | Colesie Kevin | 06 Feb 09 | 14 Feb 09 | 08 Mar 09
| February figures are 5 presentations; 3
converstions to Sign Up1 and 100% to
09 | Youngster Peter | 07 Feb 09 | |
| Sign up 2
10 | Deremp Mark | 07 Feb 09 | 16 Feb 09 | 22 Feb 09 |
11 | Doylesman Rob | 11 Feb 09 | | |
12 | Yorkel Walter | 12 Feb 09 | 22 Feb 09 | 10 Mar 09 | 12
Mar 09
13 | Styrus Wendy | 04 Mar 09 | |
| March 09 - so far - 4 Presentations; 3
converstions to Sign Up 1 100% to Sign up
14 | Goodman Susan | 05 Mar 09 | 17 Mar 09 | 27 Nov 07
| 2 with 1 so far doing the Program
15 | Taylor Marie | 06 Mar 09 | 18 Mar 09 | 28 Sep 07 |
16 | Whitbrown David | 06 Mar 09 | 07 Mar 09 | 10 Mar 09 |
11 Mar 09

The only formula I have so far is =SUMPRODUCT(((Presentations)=VALUE
("1 Jan 2009"))*((Presentations)<=VALUE("31 Jan 2009"))) which gives
me 6 presentations done in Jan 09 (I changed to SUMPRODUCT to get same
result as SUM without the ctrl-shift-enter). I need to now track these
6 and see a resultant %rate for converstions per month. So - an
another sheet converstions for Jan 09 should show =(1 Jan 09 - 31 Jan
09=6 presentations) / (signup1 "of those clients only" =2) =33%
converstion rate for Jan 09. Any help appreciated.
regards
Jonathan