Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count If combined functions
Excel 2003. I have a call log with the date call was received in a range
named "Date_Message_Left" fomatted as mm/dd/yyyy, and the Region number in a range called "Region". The region values are only 1, 2, 3, 4, or 5. I want a summary of calls to each region that were received in a given week. So if the region number is 1 and the date is between March 22, 2008 and March 29, 2008, then count it. Some cells in both ranges will be blank until new call is logged. I hope this is enough information. Trying to keep it short but still provide enough info. -- Betty H Salem, OR |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count If combined functions
Try this:
=SUMPRODUCT((Date_Message_Left=DATE(2008,3,22))*( Date_Message_Left<=DATE(2008,3,29))*(Region=1)) Ensure that the two named ranges have the same number of elements (but not complete columns). Hope this helps. Pete On Mar 25, 4:48*pm, Betty H wrote: Excel 2003. I have a call log with the date call was received in a range named "Date_Message_Left" fomatted as mm/dd/yyyy, and the Region number in a range called "Region". *The region values are only 1, 2, 3, 4, or 5. *I want a summary of calls to each region that were received in a given week. *So if the region number is 1 and the date is between March 22, 2008 and March 29, 2008, then count it. Some cells in both ranges will be blank until new call is logged. I hope this is enough information. Trying to keep it short but still provide enough info. -- Betty H Salem, OR |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count If combined functions
One thing I forgot to mention... The call log is in a different
workbook/worksheet than the summary. The call log workbook is "Toll Free Message Log.xls" and the worksheet within the book is called "Master Call Log 2008". -- Betty H Salem, OR "Pete_UK" wrote: Try this: =SUMPRODUCT((Date_Message_Left=DATE(2008,3,22))*( Date_Message_Left<=DATE(2008,3,29))*(Region=1)) Ensure that the two named ranges have the same number of elements (but not complete columns). Hope this helps. Pete On Mar 25, 4:48 pm, Betty H wrote: Excel 2003. I have a call log with the date call was received in a range named "Date_Message_Left" fomatted as mm/dd/yyyy, and the Region number in a range called "Region". The region values are only 1, 2, 3, 4, or 5. I want a summary of calls to each region that were received in a given week. So if the region number is 1 and the date is between March 22, 2008 and March 29, 2008, then count it. Some cells in both ranges will be blank until new call is logged. I hope this is enough information. Trying to keep it short but still provide enough info. -- Betty H Salem, OR |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count If combined functions
It's quite a fundamental thing not to mention !!
You could define the named ranges Date_Message_Left and Region in the workbook with the formula. The general form will be: 'full_path[file_name.xls]sheet_name'!cell_range The apostrophes are there in case you have any spaces in that string. Good luck with typing all that out. A quicker way to do it is to set up your summary sheet within the same file as the call log, defining those named ranges to suit. Then just drag the summary sheet away from the call log file and save it as another file - Excel will automatically adjust the ranges to include the path and filename for you. Hope this helps. Pete On Mar 25, 5:30*pm, Betty H wrote: One thing I forgot to mention... The call log is in a different workbook/worksheet than the summary. *The call log workbook is "Toll Free Message Log.xls" and the worksheet within the book is called "Master Call Log 2008". * -- Betty H Salem, OR "Pete_UK" wrote: Try this: =SUMPRODUCT((Date_Message_Left=DATE(2008,3,22))*( Date_Message_Left<=DATE(2*008,3,29))*(Region=1)) Ensure that the two named ranges have the same number of elements (but not complete columns). Hope this helps. Pete On Mar 25, 4:48 pm, Betty H wrote: Excel 2003. I have a call log with the date call was received in a range named "Date_Message_Left" fomatted as mm/dd/yyyy, and the Region number in a range called "Region". *The region values are only 1, 2, 3, 4, or 5. *I want a summary of calls to each region that were received in a given week. *So if the region number is 1 and the date is between March 22, 2008 and March 29, 2008, then count it. Some cells in both ranges will be blank until new call is logged. I hope this is enough information. Trying to keep it short but still provide enough info. -- Betty H Salem, OR- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF and lookup functions combined? | Excel Worksheet Functions | |||
IF and lookup functions combined? | Excel Worksheet Functions | |||
Vlookup + Address functions combined | Excel Worksheet Functions | |||
ISNA(MATCH...) and IF functions - can they be combined? | Excel Worksheet Functions | |||
combined two countif functions | Excel Discussion (Misc queries) |