Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct to count between two dates
Using Excel 2003 there are dates in F3:F19 and procedures, such as THR, in
G3:G19 on the sheet East 2010. On a second sheet in the workbook I need to count the number of 7 different procedures for each quarter. Based on feedback found in other questions I have come up with this formula: =SUMPRODUCT((('East 2010'!$G$3:$G$19="THR")*'East 2010'!$F$3:$F$19=DATE(2010,4,1)*'East 2010'!$F$3:$F$19<=DATE(2010,6,30))). I have tried different variations but am missing something as it is not function properly. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct to count between two dates
Hi,
Try this and note the different arrangement of the parenthesis =SUMPRODUCT(('East 2010'!$G$3:$G$19="THR")*('East 2010'!$F$3:$F$19=DATE(2010,4,1))*('East 2010'!$F$3:$F$19<=DATE(2010,6,30))) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Basenji" wrote: Using Excel 2003 there are dates in F3:F19 and procedures, such as THR, in G3:G19 on the sheet East 2010. On a second sheet in the workbook I need to count the number of 7 different procedures for each quarter. Based on feedback found in other questions I have come up with this formula: =SUMPRODUCT((('East 2010'!$G$3:$G$19="THR")*'East 2010'!$F$3:$F$19=DATE(2010,4,1)*'East 2010'!$F$3:$F$19<=DATE(2010,6,30))). I have tried different variations but am missing something as it is not function properly. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct to count between two dates
Mike: thank you for the input. The different arrangement of the paranthesis
appears to have made the difference. "Mike H" wrote: Hi, Try this and note the different arrangement of the parenthesis =SUMPRODUCT(('East 2010'!$G$3:$G$19="THR")*('East 2010'!$F$3:$F$19=DATE(2010,4,1))*('East 2010'!$F$3:$F$19<=DATE(2010,6,30))) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Basenji" wrote: Using Excel 2003 there are dates in F3:F19 and procedures, such as THR, in G3:G19 on the sheet East 2010. On a second sheet in the workbook I need to count the number of 7 different procedures for each quarter. Based on feedback found in other questions I have come up with this formula: =SUMPRODUCT((('East 2010'!$G$3:$G$19="THR")*'East 2010'!$F$3:$F$19=DATE(2010,4,1)*'East 2010'!$F$3:$F$19<=DATE(2010,6,30))). I have tried different variations but am missing something as it is not function properly. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count between two dates using sumproduct | Excel Worksheet Functions | |||
Sumproduct count between dates for specific day | Excel Discussion (Misc queries) | |||
I want to count the total Number of dates between two dates How? | Excel Worksheet Functions | |||
Sumproduct to get a count of dates | Excel Discussion (Misc queries) | |||
need to convert list of dates to count no. of dates by week | Excel Worksheet Functions |