Prev: Keyboard shortcut for Auto-filter
Next: create chart from daily data and add trend lines for each week
From: Basenji on 8 Apr 2010 10:08
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.
From: Mike H on 8 Apr 2010 10:19
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.
From: Basenji on 8 Apr 2010 11:11
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.
 | 
Pages: 1
Prev: Keyboard shortcut for Auto-filter
Next: create chart from daily data and add trend lines for each week