Quarterly figures with criteria
 

I have the following formula, giving me quarterly values. I need to add a
criteria "apples" to give me quarterly figures for that item. Any ideas?

{=SUM((A5005=ROUNDUP(MONTH($A$4:$A$5000)/3,0))*$D$4:$D$5000)}
This code is as an array. Is it best to use arrays or formulas?

--
AOP

Quarterly figures with criteria
 

maybe:

{=SUM((A5005=ROUNDUP(MONTH($A$4:$A$5000)/3,0))*$D$4:$D$5000)
*($x$4:$x$5000="apples")}

(still an array formula--where you use ctrl-shift-enter)

AOP wrote:

I have the following formula, giving me quarterly values. I need to add a
criteria "apples" to give me quarterly figures for that item. Any ideas?

{=SUM((A5005=ROUNDUP(MONTH($A$4:$A$5000)/3,0))*$D$4:$D$5000)}
This code is as an array. Is it best to use arrays or formulas?

--
AOP

--

Dave Peterson

Quarterly figures with criteria
 

Thanks Dave.
If I did not want to use a array formular, how would I write the formular
--
AOP


"Dave Peterson" wrote:

maybe:

{=SUM((A5005=ROUNDUP(MONTH($A$4:$A$5000)/3,0))*$D$4:$D$5000)
*($x$4:$x$5000="apples")}

(still an array formula--where you use ctrl-shift-enter)

AOP wrote:

I have the following formula, giving me quarterly values. I need to add a
criteria "apples" to give me quarterly figures for that item. Any ideas?

{=SUM((A5005=ROUNDUP(MONTH($A$4:$A$5000)/3,0))*$D$4:$D$5000)}
This code is as an array. Is it best to use arrays or formulas?

--
AOP

--

Dave Peterson

Quarterly figures with criteria
 

First, I think I screwed up the ()'s:

=SUM((A5005=ROUNDUP(MONTH($A$4:$A$5000)/3,0))*($D$4:$D$5000)
*($X$4:$X$5000="apples"))
(still array entered)

You could use =sumproduct(), but the only difference would be that you don't
need to use ctrl-shift-enter. But in my eyes, it's still an array formula.

=SUMproduct((A5005=ROUNDUP(MONTH($A$4:$A$5000)/3,0))*($D$4:$D$5000)
*($X$4:$X$5000="apples"))

or
=SUMproduct(--(A5005=ROUNDUP(MONTH($A$4:$A$5000)/3,0)),($D$4:$D$5000),
--($X$4:$X$5000="apples"))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html


AOP wrote:

Thanks Dave.
If I did not want to use a array formular, how would I write the formular
--
AOP

"Dave Peterson" wrote:

maybe:

{=SUM((A5005=ROUNDUP(MONTH($A$4:$A$5000)/3,0))*$D$4:$D$5000)
*($x$4:$x$5000="apples")}

(still an array formula--where you use ctrl-shift-enter)

AOP wrote:

I have the following formula, giving me quarterly values. I need to add a
criteria "apples" to give me quarterly figures for that item. Any ideas?

{=SUM((A5005=ROUNDUP(MONTH($A$4:$A$5000)/3,0))*$D$4:$D$5000)}
This code is as an array. Is it best to use arrays or formulas?

--
AOP

--

Dave Peterson


--

Dave Peterson

Quarterly figures with criteria
 

Thanks very Dave.
I used the sumproduct formula, that works well.
--
AOP


"Dave Peterson" wrote:

First, I think I screwed up the ()'s:

=SUM((A5005=ROUNDUP(MONTH($A$4:$A$5000)/3,0))*($D$4:$D$5000)
*($X$4:$X$5000="apples"))
(still array entered)

You could use =sumproduct(), but the only difference would be that you don't
need to use ctrl-shift-enter. But in my eyes, it's still an array formula.

=SUMproduct((A5005=ROUNDUP(MONTH($A$4:$A$5000)/3,0))*($D$4:$D$5000)
*($X$4:$X$5000="apples"))

or
=SUMproduct(--(A5005=ROUNDUP(MONTH($A$4:$A$5000)/3,0)),($D$4:$D$5000),
--($X$4:$X$5000="apples"))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html


AOP wrote:

Thanks Dave.
If I did not want to use a array formular, how would I write the formular
--
AOP

"Dave Peterson" wrote:

maybe:

{=SUM((A5005=ROUNDUP(MONTH($A$4:$A$5000)/3,0))*$D$4:$D$5000)
*($x$4:$x$5000="apples")}

(still an array formula--where you use ctrl-shift-enter)

AOP wrote:

I have the following formula, giving me quarterly values. I need to add a
criteria "apples" to give me quarterly figures for that item. Any ideas?

{=SUM((A5005=ROUNDUP(MONTH($A$4:$A$5000)/3,0))*$D$4:$D$5000)}
This code is as an array. Is it best to use arrays or formulas?

--
AOP

--

Dave Peterson


--

Dave Peterson