sumproduct

mcarter973 · Apr 9th, 2002, 11:36 AM

i want to present data by quarters: R9 = 6/30/01 and Q9 = 3/31/01. what am i missing (or doing incorrectly)?

thanks.

=SUMPRODUCT((MONTH($C$9:$N$9)<=MONTH(R$9)>MONTH(Q$9))*$C11:$N11)

Jay Petrulis · Apr 9th, 2002, 11:53 AM

Quote:

On 2002-04-09 10:36, mcarter973 wrote:
i want to present data by quarters: R9 = 6/30/01 and Q9 = 3/31/01. what am i missing (or doing incorrectly)?

thanks.

=SUMPRODUCT((MONTH($C$9:$N$9)<=MONTH(R$9)>MONTH(Q$9))*$C11:$N11)

Hi,

Try,

=SUMPRODUCT((MONTH($C$9:$N$9)<=MONTH(R$9))*(MONTH($C$9:$N$9)>MONTH(Q$9))*$C11:$N11)

You have to separate the >= and < tests into two distinct conditions.

Bye,
Jay

Mark W. · Apr 9th, 2002, 12:43 PM

{=SUM(IF(MONTH($C$9:$N$9)=MONTH($Q$9)+{1;2;3},$C$11:$N$11))}

Note: This is an array formula which must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 9th, 2002, 11:36 AM	#1
mcarter973 Board Regular Join Date: Mar 2002 Posts: 83	i want to present data by quarters: R9 = 6/30/01 and Q9 = 3/31/01. what am i missing (or doing incorrectly)? thanks. =SUMPRODUCT((MONTH($C$9:$N$9)<=MONTH(R$9)>MONTH(Q$9))*$C11:$N11)

Apr 9th, 2002, 12:43 PM	#3
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	{=SUM(IF(MONTH($C$9:$N$9)=MONTH($Q$9)+{1;2;3},$C$11:$N$11))} Note: This is an array formula which must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula.