how to count events w/in a timeframe?

[Swish7]

I am working on an XLS which tracks the success of a marketing campaign. My
question is this, given the marketing campaign and source data described
below, how can I track how many mailings were received by my test group w/in
a certain timeframe?

The marketing campaign runs for 6 months. Each month, at different times, 2
mailings are sent out. for example, on 2/1 a mailing was distributed. On 2/15
another mailing was distributed.

At the start of the campaign, 200 people were in the test group. Each month
people leave, or are added to the group.

So, if someone enters the group at the beginning, and leave it midway
through, they will receive 6 mailings. However, if they join in the second
month and stay until the end, they will receive 10.

Source Data:
The source data, that the function needs to work with are the following
columns: TEST_Group, Entry_Date, Leave_Date, and Mailing1 (date), Mailing 2,
etc.

Each row represents a customer. Ultimately, I want to list the number of
mailings received by each customer during their time in the program.

What is the best way to do this in Excel?

 

[Swish7]

To make answering easier my worksheets look somthing like this:

First tab contains:
Group Entered Entry_Date Leave_DATE
TEST NOV 11/1/2007 3/6/2008
CONTROL NOV 11/1/2007 4/5/2008
TEST DEC 12/1/2007 5/10/2008
CONTROL DEC 12/1/2007 12/1/2007
TEST JAN 1/1/2008 1/6/2008
CONTROL JAN 1/1/2008 3/6/2008
TEST FEB 2/1/2008 11/15/2008
CONTROL FEB 2/2/2008 3/6/2008
TEST MARCH 3/1/2008 4/28/2008


Second tab contains:
Sent
Mailing1 11/1/2007
Mailing2 11/28/2007
Mailing3 12/15/2007
Mailing4 1/4/2008
Mailing5 2/5/2008
Mailing6 2/16/2008
Mailing7 3/7/2008
Mailing8 3/18/2008
Mailing9 4/9/2008
Mailing10 5/1/2008

 

[Rick Rothstein \(MVP - VB\)]

Assuming your "tabs" are named Sheet1 (for you data) and Sheet2 (for your
mailing dates), that Row 1 is used for your headers on both worksheets (that
is, your actual data starts on Row 2 for each worksheet), that your data on
Sheet1 is in Column A through D in the order shown (specifically, your
Entry_Date is in Column C and your Leave_DATE is in Column D) and, finally,
that your mailing dates are in Column B on Sheet2.

Put the following formula in a blank cell on Row 2 on Sheet1 and then copy
it down as far as you want...

=SUMPRODUCT((C2<=Sheet2!B$2:B$200)*(D2>=Sheet2!B$2:B$200))

Note that this formula allows for 199 mailings (2 through 200); if you need
more, increase the 200 in both locations to whatever maximum number of
mailings you anticipate making.

Rick

 

[Herbert Seidenberg]

Excel 2003
With dynamic ranges:
http://www.freefilehosting.net/download/3ccmb

 

[Swish7]

Hi Rick, I tried your instructions below, but received a 0 for all. I don't
know if this makes a difference, but:

- I have Excel 2003
- Am not sure if I have the ATP referred to in these forums. Probably don't.
- My entry date is in column G2:G562, "sheet1"
- Leave is column N2:562, sheet1
- mailings arefrom B6:B10 on "sheet2"
- your "sheet1" is my "Pilot_Data"
- your "sheet2" is my "Ref Values"
- I put the following formula in column B, as you suggested:

=SUMPRODUCT((G2<='Ref
Values'!$B$6:$B$10)*('PILOT-DATA'!$L$2:$L$562>='PILOT-DATA'!$B$6:$B$10))

At one point I received a circuitous reference error, but I cannot reproduce
it.

Thank you for your help. I hope you have more suggestions....

 

[Rick Rothstein \(MVP - VB\)]

If I followed your setup correctly, this formula should work for your
data...

=SUMPRODUCT((G2<='Ref Values'!B$6:B$200)*(N2>='Ref Values'!B$6:B$200))

Note that I said to put this in an unused cell in Row 2, not necessarily
cell B2, and then copy down. Also, while you say your mailings are in
B6:B10, I presume this list will grow. The 200 in the above formulas will
handle mailings from B6:B200 whether there is an entry in all the cells of
that range or not. The 200 is there to handle future mailing date entries so
that you don't have to keep changing the formula with each new entry. Just
set the 200 to the biggest row number you anticipate ever using and all will
be well.

Rick

 

[Swish7]

Awesome, thank you!

Actually, I had misinterpretted your formula the first time, and inserted
errors that yours didn't contain. It works now.

If I also wanted to exclude people from another column, say Column E, which
contains Ts and Cs, how would I exclude Cs?

Thank you,
- Swish

 

[Rick Rothstein \(MVP - VB\)]

I'm guessing the Column E entries are on the Pilot_Data sheet and matched
row-for-row to the other entries. I *believe* this will do what you want...

=SUMPRODUCT((G2<='Ref Values'!B$6:B$200)*(N2>='Ref
Values'!B$6:B$200)*(E2<>"Cs"))

Rick

 

[Swish7]

Actually, I did this, and it worked as well:

=IF(E2="T",SUMPRODUCT((F2<='Ref Values'!$B$6:$B$10)*(K2>='Ref
Values'!$B$6:$B$10)),0)

Thank you very much.
- Swish