SumProduct Expert Needed

[clive2002]

I am trying to use a SumProduct Formula with one criteria being an array and it is working in part, but when i try and expand the criteria array it only works on the basic formula not the more complicated one.

Data is Structured as below

Column B Numbers from 1 to 10 repeated many times
Column C Text field showing "Up" or "Down"

I'm trying to count the Count of each Number in B and then the Count of each number in Column be with either an "Up" or "Down".

Whats killing me is that the below works

=SUMPRODUCT(--(B:B={1}))
=SUMPRODUCT(--(B:B={1}),--(C:C="Up"))

So i can count the times 1 appears in B and the times 1 Appears with "UP" in C.

This also works

=SUMPRODUCT(--(B:B={1,4}))

So i can count the times 1 and 4 Appear in B

BUT

The below produces an error

=SUMPRODUCT(--(B:B={1,4}),--(C:C="Up"))

So i can mix the array criteria on 1 product with the Column C Check?????


I have to use a sumproduct to do this and i really can't work out why it won't work.

Thanks
Clive

 

[RayH]

The arrays are different sizes.
This works.
=SUMPRODUCT(--(B:B={1,4}),--(C:C={"Up","X"}))

I put in an arbitrary value "X" to pad out the 2nd array.

 

[Brianwarnock]

It is a long time since I used sumproduct, being retired 5 years, when did it become possible to use column notation eg (A:A), it has always been possible to use whole rows (1:1) but not columns.

Thanks

Brian

 

[Brianwarnock]

I was waiting for a response to see if it was worth my contributing to this, but I'm only experienced with 2002 and maybe Sumproduct has had some major changes.

Brian

 

[RayH]

I have no idea when this change occured. If fact I don't know much about this function at all. Perhaps someone can explain the use of -- in there. The use of this function here doesn't work with out them and I cannot find an explanation in the help.

 

[Brianwarnock]

Thankyou for coming back Ray, as I said in 2002 you cannot quote whole columns infact if you say (a1:a65536) then the compiler converts this to (A:A) and then the function fails, with a NUM error I think.

Also in 2002 your answer with the 2 Value Arrays would not work as it evaluates each data cell against each value and produces arrays like

1,0;0,0;0,1;.... for the first data array then
1,0;0,0;1,0;... for the 2nd and then Ands or multiplies them in sequence before summing the result
1,0,0,0,0,0 =1

in the case quote of
{1,4} and {"up","X"} where X is a not present value it means that you would get no match for 4 and thus Sum only for the 1


The double unary -- is to coerce Sumproduct to convert the True/False results into numeric values, this can also be done with the multiply * function but the -- is more flexible however the use of the * would solve clive's problem as he would not need a second value array, however as he is obviously happy with your solution there is no need to explain further.

In 2002 you would use {1,4} {"up","up"} as your 2 value arrays and I think that you can see where this can lead, if he had wanted to count the occurrences of 1 with up or down, althougth some might prefer the use of the + function to resolve the or condition.


Brian

 

[NBVC]

The full columns can be used in Sumproduct as of release of Excel 2007, however it is very inefficient and it is recommended not to use them and to reduce the ranges to absolute largest necessary...

Having said that you can use:

=SUMPRODUCT((B:B={1,4})*(C:C="Up"))


but much more efficient would be:

=SUMPRODUCT(COUNTIFS(B:B,{1,4},C:C,"Up"))

here the COUNTIFS (XL2007 and later) is much more efficient... the SUMPRODUCT here is used to allow the COUNTIFS to process the 1 and 4 and check for either in column B. The sumproduct here is adding the results of the Countifs... it is not processing the column B and column C criteria.

 

[Brianwarnock]

Thanks NBVC I had wondered if it was 2007 but nobody had come back, and I would have suggested looking at Countif as I believe that they and Sumifs have changed for 2007.
I did mention that he could use the multiplier * but he never came back.

Thanks again for your input.

Oh one last thing
would (B:B) be different in efficiency to (B1:B365535) if you wanted to cover a dynamic but unknown larger range?, or is it just the waste of looking at empty cells the problem?

Brian

 

[NBVC]

Hi Brian,

Using a defined range like B1:B365535 is more efficient then referencing the whole milliion rows with B:B... but it would still be a drag.... as it still processes the formula as an array formula. COUNTIFS is designed differently and is not an array formula so it is more efficient in all cases.... Sometimes COUNTIFS can't be directly used... and you might want to use SUMPRODUCT... but if the ranges are large, you should consider then using helper columns and then COUNTIF on those columns and that might reduce the strain on the processors.

Even in excel 2003, if you use large range in SUMPRODUCT like A1:A63000 you will notice the strain.

 

[Brianwarnock]

Hi NBVC

can I take it that the first sentence was written with a tongue in cheek sense of humour or has the number of rows also increased? Amazing how easy it is to spot typos and syntax errors for others rather than your own. For the puzzled I said (B1:B365535) , note the erroneous 3

I think NBVC just played along.

Brian

 

[clive2002]

thanks for this guys, sorry for delay in response, been away.

 

[NBVC]

Hi NBVC

can I take it that the first sentence was written with a tongue in cheek sense of humour or has the number of rows also increased? Amazing how easy it is to spot typos and syntax errors for others rather than your own. For the puzzled I said (B1:B365535) , note the erroneous 3

I think NBVC just played along.

Brian

Sorry for the delay Brian,

Yes XL 2007 has actually increased it's rows to over 1,000,000 rows and over 16,000 columns... check this link out: New for Excel 2007

also, I think I just copied the range error you made in your post and propagated it That should have been: (B1:B65535)