Problems with a Lookup formula
 

I have a speadsheet with various columns, one containing three different
product types (NEW,USED and RESUBMITTED). I need to record how many times
NEW,USED and RESUBMITTED is used for each month. I have a column further on
in the spreadsheet with dates in.
Is there a formula/function that will pick up the three product types
seperately for each month?

Example:
Product Type Date
NEW 04/01/2006
USED 12/12/2005
RESUBMITTED 19/11/2005
RESUBMITTED 27/10/2005
RESUBMITTED 04/10/2005
NEW 11/09/2006
USED 19/08/2005
NEW 04/10/2005
NEW 11/09/2006
USED 12/12/2005
USED 19/11/2005

Problems with a Lookup formula
 

Perhaps a Pivot Table?

<Data<Pivot Table
Use: Excel
Select your data
Click the [Layout] button

ROW: Drag the Product Type field here
COLUMN: Drag the Date field here
DATA: Drag the Product Type field here
If it doesn't list as Count of Product Type...dbl-click it and set it to Count
Click [OK]

Select where you want the Pivot Table

On the Pivot Table, Right-click the Date column heading
Select: Group and show detail
Check: Year and Month

Done.
That will list the count of each Product Type by year and month

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Vix" wrote:

I have a speadsheet with various columns, one containing three different
product types (NEW,USED and RESUBMITTED). I need to record how many times
NEW,USED and RESUBMITTED is used for each month. I have a column further on
in the spreadsheet with dates in.
Is there a formula/function that will pick up the three product types
seperately for each month?

Example:
Product Type Date
NEW 04/01/2006
USED 12/12/2005
RESUBMITTED 19/11/2005
RESUBMITTED 27/10/2005
RESUBMITTED 04/10/2005
NEW 11/09/2006
USED 19/08/2005
NEW 04/10/2005
NEW 11/09/2006
USED 12/12/2005
USED 19/11/2005

Problems with a Lookup formula
 

I followed your instructions but cannot bring up any kind of check box under
the 'Group and Show Detail...' menu??
It brings up the error message '
Otherwise, this is exactly what I need.


"Ron Coderre" wrote:

Perhaps a Pivot Table?

<Data<Pivot Table
Use: Excel
Select your data
Click the [Layout] button

ROW: Drag the Product Type field here
COLUMN: Drag the Date field here
DATA: Drag the Product Type field here
If it doesn't list as Count of Product Type...dbl-click it and set it to Count
Click [OK]

Select where you want the Pivot Table

On the Pivot Table, Right-click the Date column heading
Select: Group and show detail
Check: Year and Month

Done.
That will list the count of each Product Type by year and month

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Vix" wrote:

I have a speadsheet with various columns, one containing three different
product types (NEW,USED and RESUBMITTED). I need to record how many times
NEW,USED and RESUBMITTED is used for each month. I have a column further on
in the spreadsheet with dates in.
Is there a formula/function that will pick up the three product types
seperately for each month?

Example:
Product Type Date
NEW 04/01/2006
USED 12/12/2005
RESUBMITTED 19/11/2005
RESUBMITTED 27/10/2005
RESUBMITTED 04/10/2005
NEW 11/09/2006
USED 19/08/2005
NEW 04/10/2005
NEW 11/09/2006
USED 12/12/2005
USED 19/11/2005

Problems with a Lookup formula
 

I ultimately need to count how many instances of NEW, USED and RESUBMITTED
there are for Jan, Feb, Mar, Apr,etc..

"Vix" wrote:

I followed your instructions but cannot bring up any kind of check box under
the 'Group and Show Detail...' menu??
It brings up the error message '
Otherwise, this is exactly what I need.


"Ron Coderre" wrote:

Perhaps a Pivot Table?

<Data<Pivot Table
Use: Excel
Select your data
Click the [Layout] button

ROW: Drag the Product Type field here
COLUMN: Drag the Date field here
DATA: Drag the Product Type field here
If it doesn't list as Count of Product Type...dbl-click it and set it to Count
Click [OK]

Select where you want the Pivot Table

On the Pivot Table, Right-click the Date column heading
Select: Group and show detail
Check: Year and Month

Done.
That will list the count of each Product Type by year and month

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Vix" wrote:

I have a speadsheet with various columns, one containing three different
product types (NEW,USED and RESUBMITTED). I need to record how many times
NEW,USED and RESUBMITTED is used for each month. I have a column further on
in the spreadsheet with dates in.
Is there a formula/function that will pick up the three product types
seperately for each month?

Example:
Product Type Date
NEW 04/01/2006
USED 12/12/2005
RESUBMITTED 19/11/2005
RESUBMITTED 27/10/2005
RESUBMITTED 04/10/2005
NEW 11/09/2006
USED 19/08/2005
NEW 04/10/2005
NEW 11/09/2006
USED 12/12/2005
USED 19/11/2005

Problems with a Lookup formula
 

Are your dates really dates?

Did you right-click on the Pivot Table's Date header?

It's not a check box, it's a multi-select list box, so you select Month,
hold the Ctrl key and select Year, then OK



"Vix" wrote:

I followed your instructions but cannot bring up any kind of check box under
the 'Group and Show Detail...' menu??
It brings up the error message '
Otherwise, this is exactly what I need.


"Ron Coderre" wrote:

Perhaps a Pivot Table?

<Data<Pivot Table
Use: Excel
Select your data
Click the [Layout] button

ROW: Drag the Product Type field here
COLUMN: Drag the Date field here
DATA: Drag the Product Type field here
If it doesn't list as Count of Product Type...dbl-click it and set it to Count
Click [OK]

Select where you want the Pivot Table

On the Pivot Table, Right-click the Date column heading
Select: Group and show detail
Check: Year and Month

Done.
That will list the count of each Product Type by year and month

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Vix" wrote:

I have a speadsheet with various columns, one containing three different
product types (NEW,USED and RESUBMITTED). I need to record how many times
NEW,USED and RESUBMITTED is used for each month. I have a column further on
in the spreadsheet with dates in.
Is there a formula/function that will pick up the three product types
seperately for each month?

Example:
Product Type Date
NEW 04/01/2006
USED 12/12/2005
RESUBMITTED 19/11/2005
RESUBMITTED 27/10/2005
RESUBMITTED 04/10/2005
NEW 11/09/2006
USED 19/08/2005
NEW 04/10/2005
NEW 11/09/2006
USED 12/12/2005
USED 19/11/2005