Sumproduct and Array Formula

[StephenAccountant]

Hi,

I have an Array formula which is entered with CTRL + SHIFT + ENTER.

I'm wondering if I can achieve the same result with a Sumproduct formula or
something else.

The array Formula I have is as follows:

{=SUM(IF((July!$B$11:July!$B$200>0)*(July!$F$11:July!$F$200=July!$BK$12)*(July!$K$11:July!$K$200=July!AV11)*(July!$G$11:July!$G$200=0),July!$AK$11:July!$AK$200))}

 

[T. Valko]

Try this:

=SUMPRODUCT(--(July!$B$11:$B$200>0),--(July!$F$11:$F$200=July!$BK$12),--(July!$K$11:$K$200=July!AV11),--(July!$G$11:$G$200=0),July!$AK$11:$AK$200)

 

[StephenAccountant]

Thanks - that works.

Curious - what does -- mean?

 

[T. Valko]

Curious - what does -- mean?

It has the same effect as multiplying the arrays together like you did in
your other formula, only it's slightly more efficient.

(A1:A10="X")*(B1:B10="Y")

The above returns an array of 1s and 0s

--(A1:A10="X") does the same thing more efficiently.

See this:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html