+ Reply to Thread
Results 1 to 5 of 5

Conditional Sum of Quarters

  1. #1
    Andy
    Guest

    Conditional Sum of Quarters

    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

  2. #2
    Bob Phillips
    Guest

    Re: Conditional Sum of Quarters

    =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" <sharpie_dog@hotmail.com> wrote in message
    news:2C1974D9-3583-42BC-861B-1DEB7EF0D8EB@microsoft.com...
    > 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




  3. #3
    Ron Coderre
    Guest

    RE: Conditional Sum of Quarters

    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


  4. #4
    Andy
    Guest

    Re: Conditional Sum of Quarters

    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" <sharpie_dog@hotmail.com> wrote in message
    > news:2C1974D9-3583-42BC-861B-1DEB7EF0D8EB@microsoft.com...
    > > 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

    >
    >
    >


  5. #5
    Andy
    Guest

    RE: Conditional Sum of Quarters

    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


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1