finding # of occurrences of information between two dates
 

I am trying to create a formula that will identify the number of sales for a
particular course on a monthly basis. This information will be fed to cells
on another sheet within the same workbook.

I know how to count the number of occurrences for a particular text stream,
but having that total based on the date is eluding me. I thought vlookup
might work, but I can't seem to get my head around how to make it calculate
correctly.

Any help would be GREATLY appreciated!


1/1/2007 Millwork Course - Complete Package
1/4/2007 Math For Enterprising Minds - Complete Package
2/24/2007 Millwork Course - Complete Package
3/1/2007 Principles of Proessional Selling - Complete Package
4/6/2007 Millwork Course - Complete Package

finding # of occurrences of information between two dates
 

Try this:

Count of Millwork Course - Complete Package for the month of Jan 2007:


=SUMPRODUCT(--(A1:A10=DATE(2007,1,1)),--(A1:A10<=DATE(2007,1,31)),--(B1:B10="Millwork
Course - Complete Package"))

Better to use cells to hold the criteria:

D1 = start date = 1/1/2007
E1 = end date = 1/31/2007
F1 = Millwork Course - Complete Package

=SUMPRODUCT(--(A1:A10=D1),--(A1:A10<=E1),--(B1:B10=F1))



--
Biff
Microsoft Excel MVP


"JPLong" wrote in message
...
I am trying to create a formula that will identify the number of sales for
a
particular course on a monthly basis. This information will be fed to
cells
on another sheet within the same workbook.

I know how to count the number of occurrences for a particular text
stream,
but having that total based on the date is eluding me. I thought vlookup
might work, but I can't seem to get my head around how to make it
calculate
correctly.

Any help would be GREATLY appreciated!


1/1/2007 Millwork Course - Complete Package
1/4/2007 Math For Enterprising Minds - Complete Package
2/24/2007 Millwork Course - Complete Package
3/1/2007 Principles of Proessional Selling - Complete Package
4/6/2007 Millwork Course - Complete Package

finding # of occurrences of information between two dates
 

Try looking at COUNTIF and SUMIF in the help file.
--
Best Regards,

Luke M


"JPLong" wrote:

I am trying to create a formula that will identify the number of sales for a
particular course on a monthly basis. This information will be fed to cells
on another sheet within the same workbook.

I know how to count the number of occurrences for a particular text stream,
but having that total based on the date is eluding me. I thought vlookup
might work, but I can't seem to get my head around how to make it calculate
correctly.

Any help would be GREATLY appreciated!


1/1/2007 Millwork Course - Complete Package
1/4/2007 Math For Enterprising Minds - Complete Package
2/24/2007 Millwork Course - Complete Package
3/1/2007 Principles of Proessional Selling - Complete Package
4/6/2007 Millwork Course - Complete Package

finding # of occurrences of information between two dates
 

Oops, didn't see that you wanted on a monthly basis. Valko is right in using
a SUMPRODUCT then. My apologies.
--
Best Regards,

Luke M


"Luke M" wrote:

Try looking at COUNTIF and SUMIF in the help file.
--
Best Regards,

Luke M


"JPLong" wrote:

I am trying to create a formula that will identify the number of sales for a
particular course on a monthly basis. This information will be fed to cells
on another sheet within the same workbook.

I know how to count the number of occurrences for a particular text stream,
but having that total based on the date is eluding me. I thought vlookup
might work, but I can't seem to get my head around how to make it calculate
correctly.

Any help would be GREATLY appreciated!


1/1/2007 Millwork Course - Complete Package
1/4/2007 Math For Enterprising Minds - Complete Package
2/24/2007 Millwork Course - Complete Package
3/1/2007 Principles of Proessional Selling - Complete Package
4/6/2007 Millwork Course - Complete Package