| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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" wrote in message ... 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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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(DATE VALUE($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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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" wrote in message ... 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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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(DATE VALUE($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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Keeping conditional formatting when sorting | Excel Discussion (Misc queries) | |||
| Conditional Formatting when inserting a row | Excel Worksheet Functions | |||
| Conditional Format Not Working | Excel Discussion (Misc queries) | |||
| Conditional formula - language needed | Excel Worksheet Functions | |||
| Conditional formatting not available in Excel | Excel Discussion (Misc queries) | |||
Linear Mode
