Help with this needed!!

[Dawn Killick]

I have a workbook with two worksheets.

Sheet 1 is an itemisation for December 2003 for customers who have cancelled
a subscription to a DVD/magazine service (reason for cancelling, number of
cancellations for each reason).

Sheet 2 is a complete listing of all customers who have ever cancelled along
with their cancellation date and reason for cancelling. This sheet contains
data going back three years.

What I would like to do is use a lookup to populate the number of
cancellations for each reason in sheet 1 from the data held in sheet 2.

I tried messing around with =sumif but I'm struggling with it because
somewhere in the equation I need to include something along the lines of
"where date_cancelled >=01/12/2003 and date_cancelled<=31/12/2003".

Can anybody point me in the right direction?

TIA.

 

[Norman Harker]

Hi Dawn!

What was wrong with the following response by Harlan Grove:

=SUMPRODUCT((ReasonCode=Whatever)*(CancelDate>=DATE(2003,12,1))*(Cance
lDate<=DATE(2003,12,31)))

Posted in response to same question to Comp.apps.spreadsheets 14 hours
ago.

Or do you need more assistance with it?


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Dawn Killick]

Hi Dawn!

What was wrong with the following response by Harlan Grove:

=SUMPRODUCT((ReasonCode=Whatever)*(CancelDate>=DATE(2003,12,1))*(Cance
lDate<=DATE(2003,12,31)))

Posted in response to same question to Comp.apps.spreadsheets 14 hours
ago.

Or do you need more assistance with it?

Nothing wrong with it that I was aware of, I was just looking for a
contingency plan in case Harlan's suggestion fell over on me! Not that I
think it would

 

[Dawn Killick]

Hi Dawn!

What was wrong with the following response by Harlan Grove:

=SUMPRODUCT((ReasonCode=Whatever)*(CancelDate>=DATE(2003,12,1))*(Cance
lDate<=DATE(2003,12,31)))

Posted in response to same question to Comp.apps.spreadsheets 14 hours
ago.

Or do you need more assistance with it?

Ok, I can't get Harlan's suggestion to work which is probably a reflection
on my explanatory description of what I needed to achieve rather than
Harlan's Excel knowledge.

A crude illustration:

Cancellation_Reason Number_of_cancellations
1
2
3
etc

Call this Sheet 1 (December 2003). A formula needs to be in each cell in
the Number_of_cancellations field which pulls data through from Sheet 2
which would look thus:

Subscriber ID Cancellation_date Reason_code
234567 10/12/2003 1
345678 21/11/2003 2
456789 10/10/2003 1
etc

Now, for Sheet 1 (December 2003) the formula I need would pick up that
although Sheet 2 has two entries for Reason_code 1, it would return the
answer of 1 because I'm only looking for cancellations in December.

I've probably muddied the waters even more with this explanation but it's
the best I can do!

Help anyone?

TIA.

 

[Dave Peterson]

Another option may be to use Data|Pivottable

Select your range (include only the lastrow of headers)
Data|pivottable
Follow the wizard until you get a dialog with a Layout button on it.
hit that Layout button
Drag the Cancellation header button to the Row field
drag the reason code header to the Column field
drag the reason code (once again) to the Data field
(if it appears as "Sum of Reason_code" double click on it and change it to
"count")

Finish up the wizard

Now right click on the Cancellation Date header
and choose "group and show detail"
Then Group
by Months AND Years

Then ok.

This doesn't look too nice in the post, but this is what I got:

Count of Reason_code Reason_code
Years Cancellation_date 1 2 Grand Total
2003 Oct 1 1
Nov 1 1
Dec 1 1
Grand Total 2 1 3

 

[Dawn Killick]

Another option may be to use Data|Pivottable

Select your range (include only the lastrow of headers)
Data|pivottable
Follow the wizard until you get a dialog with a Layout button on it.
hit that Layout button
Drag the Cancellation header button to the Row field
drag the reason code header to the Column field
drag the reason code (once again) to the Data field
(if it appears as "Sum of Reason_code" double click on it and change it to
"count")

Finish up the wizard

Now right click on the Cancellation Date header
and choose "group and show detail"
Then Group
by Months AND Years

Then ok.

This doesn't look too nice in the post, but this is what I got:

Count of Reason_code Reason_code
Years Cancellation_date 1 2 Grand Total
2003 Oct 1 1
Nov 1 1
Dec 1 1
Grand Total 2 1 3

Thanks for the quick response, I'll try that in the morning when the vodka's
out of my system