Help with a Sum and an Array

[Pablo]

I am needing a little help with a function that looks a Col A and takes the
last value before 1 summing Col B. counting backwards to the previous 1 in
Col C. I was thinking Col C. might look something like this.

Cell C3 = if(or(A4=1, A4=""), Sum(B3:B(A3-(A3+1))),"")
Cell C8 = if(or(A9=1, A9=""), Sum(B8:B(A8-(A8+1))),"")

Col A. Col B. Col C.
1 1
2 1
3 0 2
1 1
2 1
3 1
4 1
5 1 5
1 0

 

[driller]

Pablo,

Do you mean A4="" or A4=1 then Cell C3 shall have a sum ?
if A4=0, then what will happen otherwise.

regards

 

[Pablo]

Col A. is counting repeating values in another column. When the value stops
repeating then the counter is reset to 1. It would always go from 1 to n. I
included "" so that there is stopping point after the last record.

I just cannot find a way to look back on a selected range in Col B. from the
value in Col A., in this case A3 = 3, A = 5.

Maybe I am taking the wrong approach or their is a simpler way. Any ideas
are greatly appreciated.

Pablo

 

[T. Valko]

It looks like you want to return the max value of a sequence that resets
after the sequence starts over again.

It would always go from 1 to n.

Assuming there is *always* an empty cell after the end of data.

With your data in A1:An

Enter this formula B1 and copy down to the end of data:

=IF(A2<=A1,LOOKUP(1E100,A$1:A1),"")

 

[Pablo]

Bill, Thank you

The only other thing I need to do is sum the values in Col. B for that
particular sequence in Col A. What I have below in Col C is an example of the
result I am looking for. I keep getting all screwed up with this.

Thanks

 

[T. Valko]

Ok, same caveat, there is *always* an empty cell after the end of data in
column B.

Enter this formula in C1:

=IF(AND(B1=1,B2=1),"",1)

Enter this formula in C2 and copy down to the end of data:

=IF(B2=0,"",IF(B3=0,SUM(B$1:B2)-SUM(C$1:C1),""))

 

[Pablo]

Bill,

I really appreciate your help to the point, but I think I may have not been
clear in my explanation.

Essentially, Col A is sequence of varying lengths. Col B is the results of a
test, 1 and 0. With Col C I am looking to sum the test results from Col B for
each sequence of Col A. What I am struggling with is summing the test results
in Col C for each sequence.

caveat, there is *always* an empty cell after the end of data in
column B.

Col A. Col B. Col C.
1 1
2 1
3 0
1 1
2 1
3 1
4 1
5 1
1 0

 

[T. Valko]

Using the formulas I suggested these are the results they produce:

A.....B.....C
1......1.......
2......1.....2
3......0......
1......1......
2......1......
3......1......
4......1......
5......1.....5
1......0......

Are those the results you're looking for?

 

[driller]

Pablo,

caveat, there is *always* an empty cell after the end of data in column B.

Col A. Col B. Col C.
1 1
2 1
3 0
1 1
2 1
3 1
4 1
5 1
1 0

As mentioned, Col B is the results of a test, 1 and 0.

from above sample, please show where is the "empty cell" after the end of
the said Data.

Only 0's and 1's are shown in Col. B. Col A do not show any "empty cell" as
well.

regards,

 

[Pablo]

Very close. Only thing would be to sum at the max number of the sequence
whether it was a 1 or 0. So in the first sequence the summation in Col C
would be at A3, instead A2.

 

[Pablo]

Hi driller,

The only empty cell would be at the end of the data. There would never be an
empty cell within the data itself. Again, Col A is a series of sequences of
varying lengths. Col B is testing something from another source, 1 and 0. I
need Col C to summarize Col B for *each sequence* of Col A. The max value for
each sequence in Col A should set the range of the summation.

 

[driller]

Pablo,

i am now confused.

Biff's reply seems ok with me.

Since the last from the series of data are "empty cells" and has nothing to
do with the location of sum at all; then
maybe you can show again here the final results <location> in Column C along
with Col A and B.

regrads,

 

[Pablo]

Not a problem. Apologies if the explanations have not been very clear. Here
is an example of what I am looking in with Col C.

Note... the sum range is set based on the "max value" of the sequence in Col
A. The first sequence ends on 3 and the sum of Col B is 2, (1,1,0). The
second sequence ends on 5 and the sum is 5 (1,1,1,1,1). The third sequence
ends at 1 and the sum is 0, (0).

What I experienced with Bill's solution is the summation for the first
sequence occurred at the last 1 of the sequence or range, or at the second
number (Col A) in the sequence instead of the last, 3.

I also found that if I changed the 0 to 1 in Col B it summed both the first
and second sequences together for a result of 8. I need to have them maintain
their independence.

Hope this helps.


Col A. Col B. Col C.
1......... 1
2......... 1
3......... 0......... 2
1......... 1
2......... 1
3......... 1
4......... 1
5......... 1......... 5
1......... 0......... 0

 

[T. Valko]

Try these:

C1:

=IF(A1<A2,"",1)

C2 and copied down:

=IF(A2>=A3,SUM(B$1:B2)-SUM(C$1:C1),"")

 

[Pablo]

This is it! Thank you very much. I appreciate your patience.

I kept trying to think I needed an array to make this work.

 

[T. Valko]

Good deal. Thanks for the feedback!