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
0
4/27/2006 4:40:03 PM
excel.newusers 15348 articles. 2 followers. Follow

4 Replies
213 Views

Similar Articles

[PageSpeed] 53

=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


0
bob.phillips1 (6510)
4/27/2006 4:57:32 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
0
4/27/2006 5:05:02 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" <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
> 
> 
> 
0
4/27/2006 5:22:02 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
0
4/27/2006 5:23:01 PM
Reply:

Similar Artilces: