Sumproduct and Array Formula

  • Thread starter StephenAccountant
  • Start date
S

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

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)
 
T

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top