Sumproduct and Array Formula
 

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$2000)*(July!$F$11:Ju ly!$F$200=July!$BK$12)*(July!$K$11:July!$K$200=Jul y!AV11)*(July!$G$11:July!$G$200=0),July!$AK$11:Jul y!$AK$200))}

Sumproduct and Array Formula
 

Try this:

=SUMPRODUCT(--(July!$B$11:$B$2000),--(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)


--
Biff
Microsoft Excel MVP


"StephenAccountant" wrote in
message ...
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$2000)*(July!$F$11:Ju ly!$F$200=July!$BK$12)*(July!$K$11:July!$K$200=Jul y!AV11)*(July!$G$11:July!$G$200=0),July!$AK$11:Jul y!$AK$200))}

Sumproduct and Array Formula
 

Thanks - that works.

Curious - what does -- mean?

"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(July!$B$11:$B$2000),--(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)


--
Biff
Microsoft Excel MVP


"StephenAccountant" wrote in
message ...
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$2000)*(July!$F$11:Ju ly!$F$200=July!$BK$12)*(July!$K$11:July!$K$200=Jul y!AV11)*(July!$G$11:July!$G$200=0),July!$AK$11:Jul y!$AK$200))}

Sumproduct and Array Formula
 

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

--
Biff
Microsoft Excel MVP


"StephenAccountant" wrote in
message ...
Thanks - that works.

Curious - what does -- mean?

"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(July!$B$11:$B$2000),--(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)


--
Biff
Microsoft Excel MVP


"StephenAccountant" wrote
in
message ...
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$2000)*(July!$F$11:Ju ly!$F$200=July!$BK$12)*(July!$K$11:July!$K$200=Jul y!AV11)*(July!$G$11:July!$G$200=0),July!$AK$11:Jul y!$AK$200))}