Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding MIN value in a column between Two Dates in different columns | Excel Worksheet Functions | |||
Finding Certain Dates | New Users to Excel | |||
finding a range of dates to total | Excel Discussion (Misc queries) | |||
Finding data by dates | Excel Worksheet Functions | |||
Formula to find information between 2 dates | Excel Worksheet Functions |