Conditional Sum of Quarters

Andy · 04-27-2006, 12:45 PM

I have tried the conditional sum to calculate quartely results for the
following:

Measures Month Population Sample Population Missing/Invalid
Population Numerator Missing/Invalid Numerator Denominator Observed Rate
Discharge instructions JAN 2005 63 63 0 2 0 48 4%
Discharge instructions FEB 2005 69 69 0 7 0 48 15%
Discharge instructions MAR 2005 72 72 0 6 0 50 12%
LVF assessment JAN 2005 63 63 0 51 0 60 85%
LVF assessment FEB 2005 69 69 0 56 0 65 86%
LVF assessment MAR 2005 72 72 0 59 0 66 89%
ACEI or ARB for LVSD JAN 2005 63 63 0 16 0 24 67%
ACEI or ARB for LVSD FEB 2005 69 69 0 15 0 26 58%
ACEI or ARB for LVSD MAR 2005 72 72 0 25 0 37 68%
Adult smoking cessation advice/counseling JAN 2005 63 63 0 2 0 7 29%
Adult smoking cessation advice/counseling FEB 2005 69 69 0 2 0 5 40%
Adult smoking cessation advice/counseling MAR 2005 72 72 0 5 0 9 56%
Discharge instructions APR 2005 65 65 0 7 0 51 14%
Discharge instructions MAY 2005 62 62 0 13 0 46 28%
Discharge instructions JUN 2005 56 56 0 10 0 40 25%
LVF assessment APR 2005 65 65 0 58 0 62 94%
LVF assessment MAY 2005 62 62 0 53 0 57 93%
LVF assessment JUN 2005 56 56 0 44 0 50 88%
ACEI or ARB for LVSD APR 2005 65 65 0 19 0 35 54%
ACEI or ARB for LVSD MAY 2005 62 62 0 20 0 32 63%
ACEI or ARB for LVSD JUN 2005 56 56 0 18 0 28 64%
Adult smoking cessation advice/counseling APR 2005 65 65 0 2 0 7 29%
Adult smoking cessation advice/counseling MAY 2005 62 62 0 4 0 7 57%
Adult smoking cessation advice/counseling JUN 2005 56 56 0 3 0 6 50%

But I am unable to say I want Measures=Discharge Instructions, Month to
equal, Jan 2005, Feb 2005, Mar 2005.

Please help
--
Thanks,
Andy

Bob Phillips · 04-27-2006, 01:10 PM

=SUMPRODUCT(--(A2:A25="Discharge
Instructions"),--(MONTH(B2:B25)>=1),--(MONTH(B2:B25)<=3),C2:C25)

this is assuming that it is column C that you want to SUM.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Andy" <[email protected]> wrote in message
news:[email protected]...
> I have tried the conditional sum to calculate quartely results for the
> following:
>
> Measures Month Population Sample Population Missing/Invalid
> Population Numerator Missing/Invalid Numerator Denominator Observed Rate
> Discharge instructions JAN 2005 63 63 0 2 0 48 4%
> Discharge instructions FEB 2005 69 69 0 7 0 48 15%
> Discharge instructions MAR 2005 72 72 0 6 0 50 12%
> LVF assessment JAN 2005 63 63 0 51 0 60 85%
> LVF assessment FEB 2005 69 69 0 56 0 65 86%
> LVF assessment MAR 2005 72 72 0 59 0 66 89%
> ACEI or ARB for LVSD JAN 2005 63 63 0 16 0 24 67%
> ACEI or ARB for LVSD FEB 2005 69 69 0 15 0 26 58%
> ACEI or ARB for LVSD MAR 2005 72 72 0 25 0 37 68%
> Adult smoking cessation advice/counseling JAN 2005 63 63 0 2 0 7 29%
> Adult smoking cessation advice/counseling FEB 2005 69 69 0 2 0 5 40%
> Adult smoking cessation advice/counseling MAR 2005 72 72 0 5 0 9 56%
> Discharge instructions APR 2005 65 65 0 7 0 51 14%
> Discharge instructions MAY 2005 62 62 0 13 0 46 28%
> Discharge instructions JUN 2005 56 56 0 10 0 40 25%
> LVF assessment APR 2005 65 65 0 58 0 62 94%
> LVF assessment MAY 2005 62 62 0 53 0 57 93%
> LVF assessment JUN 2005 56 56 0 44 0 50 88%
> ACEI or ARB for LVSD APR 2005 65 65 0 19 0 35 54%
> ACEI or ARB for LVSD MAY 2005 62 62 0 20 0 32 63%
> ACEI or ARB for LVSD JUN 2005 56 56 0 18 0 28 64%
> Adult smoking cessation advice/counseling APR 2005 65 65 0 2 0 7 29%
> Adult smoking cessation advice/counseling MAY 2005 62 62 0 4 0 7 57%
> Adult smoking cessation advice/counseling JUN 2005 56 56 0 3 0 6 50%
>
>
> But I am unable to say I want Measures=Discharge Instructions, Month to
> equal, Jan 2005, Feb 2005, Mar 2005.
>
> Please help
> --
> Thanks,
> Andy

Ron Coderre · 04-27-2006, 01:10 PM

Here's something to try....

Using your data in cells A1:I25
(Assuming Col_B contains text, not dates)

A27: Discharge Instructions
B27: 1 (the quarter reference)
C27:
=SUMPRODUCT(($A$2:$A$25=$A$27)*(CEILING(MONTH(DATEVALUE($B$2:$B$25))/3,1)=$B$27)*C2:C25)

Or..if the Month field contains dates:
C27:
=SUMPRODUCT(($A$2:$A$25=$A$27)*(CEILING(MONTH($B$2:$B$25)/3,1)=$B$27)*C2:C25)

That formula sums the Population column where Measures="Discharge
Instructions " and the Month is in the First Qtr.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

"Andy" wrote:

> I have tried the conditional sum to calculate quartely results for the
> following:
>
> Measures Month Population Sample Population Missing/Invalid
> Population Numerator Missing/Invalid Numerator Denominator Observed Rate
> Discharge instructions JAN 2005 63 63 0 2 0 48 4%
> Discharge instructions FEB 2005 69 69 0 7 0 48 15%
> Discharge instructions MAR 2005 72 72 0 6 0 50 12%
> LVF assessment JAN 2005 63 63 0 51 0 60 85%
> LVF assessment FEB 2005 69 69 0 56 0 65 86%
> LVF assessment MAR 2005 72 72 0 59 0 66 89%
> ACEI or ARB for LVSD JAN 2005 63 63 0 16 0 24 67%
> ACEI or ARB for LVSD FEB 2005 69 69 0 15 0 26 58%
> ACEI or ARB for LVSD MAR 2005 72 72 0 25 0 37 68%
> Adult smoking cessation advice/counseling JAN 2005 63 63 0 2 0 7 29%
> Adult smoking cessation advice/counseling FEB 2005 69 69 0 2 0 5 40%
> Adult smoking cessation advice/counseling MAR 2005 72 72 0 5 0 9 56%
> Discharge instructions APR 2005 65 65 0 7 0 51 14%
> Discharge instructions MAY 2005 62 62 0 13 0 46 28%
> Discharge instructions JUN 2005 56 56 0 10 0 40 25%
> LVF assessment APR 2005 65 65 0 58 0 62 94%
> LVF assessment MAY 2005 62 62 0 53 0 57 93%
> LVF assessment JUN 2005 56 56 0 44 0 50 88%
> ACEI or ARB for LVSD APR 2005 65 65 0 19 0 35 54%
> ACEI or ARB for LVSD MAY 2005 62 62 0 20 0 32 63%
> ACEI or ARB for LVSD JUN 2005 56 56 0 18 0 28 64%
> Adult smoking cessation advice/counseling APR 2005 65 65 0 2 0 7 29%
> Adult smoking cessation advice/counseling MAY 2005 62 62 0 4 0 7 57%
> Adult smoking cessation advice/counseling JUN 2005 56 56 0 3 0 6 50%
>
>
> But I am unable to say I want Measures=Discharge Instructions, Month to
> equal, Jan 2005, Feb 2005, Mar 2005.
>
> Please help
> --
> Thanks,
> Andy

Andy · 04-27-2006, 01:25 PM

THANK YOU SOOOOOOOOO Much, I have been fighting this thing for 2 hours & it
was a pretty simple fix. I greatly apprecate your help.
--
Thanks,
Andy

"Bob Phillips" wrote:

> =SUMPRODUCT(--(A2:A25="Discharge
> Instructions"),--(MONTH(B2:B25)>=1),--(MONTH(B2:B25)<=3),C2:C25)
>
> this is assuming that it is column C that you want to SUM.
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Andy" <[email protected]> wrote in message
> news:[email protected]...
> > I have tried the conditional sum to calculate quartely results for the
> > following:
> >
> > Measures Month Population Sample Population Missing/Invalid
> > Population Numerator Missing/Invalid Numerator Denominator Observed Rate
> > Discharge instructions JAN 2005 63 63 0 2 0 48 4%
> > Discharge instructions FEB 2005 69 69 0 7 0 48 15%
> > Discharge instructions MAR 2005 72 72 0 6 0 50 12%
> > LVF assessment JAN 2005 63 63 0 51 0 60 85%
> > LVF assessment FEB 2005 69 69 0 56 0 65 86%
> > LVF assessment MAR 2005 72 72 0 59 0 66 89%
> > ACEI or ARB for LVSD JAN 2005 63 63 0 16 0 24 67%
> > ACEI or ARB for LVSD FEB 2005 69 69 0 15 0 26 58%
> > ACEI or ARB for LVSD MAR 2005 72 72 0 25 0 37 68%
> > Adult smoking cessation advice/counseling JAN 2005 63 63 0 2 0 7 29%
> > Adult smoking cessation advice/counseling FEB 2005 69 69 0 2 0 5 40%
> > Adult smoking cessation advice/counseling MAR 2005 72 72 0 5 0 9 56%
> > Discharge instructions APR 2005 65 65 0 7 0 51 14%
> > Discharge instructions MAY 2005 62 62 0 13 0 46 28%
> > Discharge instructions JUN 2005 56 56 0 10 0 40 25%
> > LVF assessment APR 2005 65 65 0 58 0 62 94%
> > LVF assessment MAY 2005 62 62 0 53 0 57 93%
> > LVF assessment JUN 2005 56 56 0 44 0 50 88%
> > ACEI or ARB for LVSD APR 2005 65 65 0 19 0 35 54%
> > ACEI or ARB for LVSD MAY 2005 62 62 0 20 0 32 63%
> > ACEI or ARB for LVSD JUN 2005 56 56 0 18 0 28 64%
> > Adult smoking cessation advice/counseling APR 2005 65 65 0 2 0 7 29%
> > Adult smoking cessation advice/counseling MAY 2005 62 62 0 4 0 7 57%
> > Adult smoking cessation advice/counseling JUN 2005 56 56 0 3 0 6 50%
> >
> >
> > But I am unable to say I want Measures=Discharge Instructions, Month to
> > equal, Jan 2005, Feb 2005, Mar 2005.
> >
> > Please help
> > --
> > Thanks,
> > Andy
>
>
>

Andy · 04-27-2006, 01:30 PM

THANK YOU SOOOOOOOOO Much, I have been fighting this thing for 2 hours & it
was a pretty simple fix. I greatly apprecate your help.
--
Thanks,
Andy

"Ron Coderre" wrote:

> Here's something to try....
>
> Using your data in cells A1:I25
> (Assuming Col_B contains text, not dates)
>
> A27: Discharge Instructions
> B27: 1 (the quarter reference)
> C27:
> =SUMPRODUCT(($A$2:$A$25=$A$27)*(CEILING(MONTH(DATEVALUE($B$2:$B$25))/3,1)=$B$27)*C2:C25)
>
> Or..if the Month field contains dates:
> C27:
> =SUMPRODUCT(($A$2:$A$25=$A$27)*(CEILING(MONTH($B$2:$B$25)/3,1)=$B$27)*C2:C25)
>
> That formula sums the Population column where Measures="Discharge
> Instructions " and the Month is in the First Qtr.
>
> Does that help?
>
> ***********
> Regards,
> Ron
>
> XL2002, WinXP-Pro
>
>
> "Andy" wrote:
>
> > I have tried the conditional sum to calculate quartely results for the
> > following:
> >
> > Measures Month Population Sample Population Missing/Invalid
> > Population Numerator Missing/Invalid Numerator Denominator Observed Rate
> > Discharge instructions JAN 2005 63 63 0 2 0 48 4%
> > Discharge instructions FEB 2005 69 69 0 7 0 48 15%
> > Discharge instructions MAR 2005 72 72 0 6 0 50 12%
> > LVF assessment JAN 2005 63 63 0 51 0 60 85%
> > LVF assessment FEB 2005 69 69 0 56 0 65 86%
> > LVF assessment MAR 2005 72 72 0 59 0 66 89%
> > ACEI or ARB for LVSD JAN 2005 63 63 0 16 0 24 67%
> > ACEI or ARB for LVSD FEB 2005 69 69 0 15 0 26 58%
> > ACEI or ARB for LVSD MAR 2005 72 72 0 25 0 37 68%
> > Adult smoking cessation advice/counseling JAN 2005 63 63 0 2 0 7 29%
> > Adult smoking cessation advice/counseling FEB 2005 69 69 0 2 0 5 40%
> > Adult smoking cessation advice/counseling MAR 2005 72 72 0 5 0 9 56%
> > Discharge instructions APR 2005 65 65 0 7 0 51 14%
> > Discharge instructions MAY 2005 62 62 0 13 0 46 28%
> > Discharge instructions JUN 2005 56 56 0 10 0 40 25%
> > LVF assessment APR 2005 65 65 0 58 0 62 94%
> > LVF assessment MAY 2005 62 62 0 53 0 57 93%
> > LVF assessment JUN 2005 56 56 0 44 0 50 88%
> > ACEI or ARB for LVSD APR 2005 65 65 0 19 0 35 54%
> > ACEI or ARB for LVSD MAY 2005 62 62 0 20 0 32 63%
> > ACEI or ARB for LVSD JUN 2005 56 56 0 18 0 28 64%
> > Adult smoking cessation advice/counseling APR 2005 65 65 0 2 0 7 29%
> > Adult smoking cessation advice/counseling MAY 2005 62 62 0 4 0 7 57%
> > Adult smoking cessation advice/counseling JUN 2005 56 56 0 3 0 6 50%
> >
> >
> > But I am unable to say I want Measures=Discharge Instructions, Month to
> > equal, Jan 2005, Feb 2005, Mar 2005.
> >
> > Please help
> > --
> > Thanks,
> > Andy