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.

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.

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.