Sumproduct

[este994]

Hello.

I have the following formula:

=SUMPRODUCT(('Retailer1!$K$2:$K$2263=CONCATENATE(A5,"LY"))*('Retailer1'!$L$2:$L$2263<>0))

And I want it to give me the number of times the number changes from 0 to a rather than counting all the values higher than 0.

Can anyone helpme?

[martindwilson]

what do you mean by "changes from 0" that as it stands counts + and -ve nos

[este994]

Yea, but what I want my result to show is the amount of times (in the given range L) the values change from 0 to 1.

my range looks liek this:
0
0
0
0
0
0
1
1
1
1
0
0
0
1
1
1
1
0
0
0
1
1
1

So in this case I would want the answer to be 3. Does that make sense?

[daddylonglegs]

Do you still want to take into account the column K condition? Try like this

=SUMPRODUCT(('Retailer1!$K$2:$K$2262=CONCATENATE(A5,"LY"))*('Retailer1'!$L$2:$L$2262=0)*('Retailer1!$K$3:$K$2263=CONCATENATE(A5,"LY"))*('Retailer1'!$L$3:$L$2263=1))

Notice that for the third and fourth conditions the ranges need to be "offset" by 1

[este994]

Thank you. But unfortunately the result comes up with 0... any ideas?

[martindwilson]

=SUMPRODUCT(--(FREQUENCY(IF(A1:A24=0,ROW(A1:A24)),IF(A1:A24<>0,ROW(A1:A24)))>0)) array entered will give the number of blocks of zeros