Searching an array and finding sum

[Tre_cool]

I have a spread sheet with values in one column and a second column with just "Yes" or 0 depending on if column A satisfied a criteria. I would like to search through column B and find the sum of column A between the "Yes" rows. For example I would like to find the sum of Column A between line 1 and 4 and the sum between 4 and 6. It must work when the "Yes"'s are in different lines as well.
EG.
line Col A Col B
1 3 Yes
2 6 0
3 8 0
4 11 Yes
5 12 0
6 16 Yes

Please help as I've been struggling with this for a little while.

Thanks in advance!
Trev

[Tom Ogilvy]

What do you mean by between. Is =Sum(A1:A4) included in the sum or is only
=Sum(A2:A3) for the first group.

Where do the results go?

Are the string "Yes" and the number zero (0) produced by a formula? Are the
formulas currently in the cells in column B?


--
Regards,
Tom Ogilvy

"Tre_cool" <[email protected]> wrote in
message news:[email protected]...
>
> I have a spread sheet with values in one column and a second column with
> just "Yes" or 0 depending on if column A satisfied a criteria. I would
> like to search through column B and find the sum of column A between
> the "Yes" rows. For example I would like to find the sum of Column A
> between line 1 and 4 and the sum between 4 and 6. It must work when the
> "Yes"'s are in different lines as well.
> EG.
> line Col A Col B
> 1 3 Yes
> 2 6 0
> 3 8 0
> 4 11 Yes
> 5 12 0
> 6 16 Yes
>
> Please help as I've been struggling with this for a little while.
>
> Thanks in advance!
> Trev
>
>
> --
> Tre_cool
> ------------------------------------------------------------------------
> Tre_cool's Profile:
http://www.excelforum.com/member.php...o&userid=26416
> View this thread: http://www.excelforum.com/showthread...hreadid=396852
>

[Tre_cool]

Tom,
It's the sum between the "Yes"'s, so =Sum(A2:A3). The formula to generate the "Yes" and 0 are in the cells in Column B.


The results will just go in Column C. I hope that makes sense.

Thanks
Trevor

[Tom Ogilvy]

Sub SumBetween()
Set rng = Columns(2).SpecialCells(xlFormulas, xlTextValues)
i = 0
For Each ar In rng.Areas
i = i + 1
If i <> 1 Then
Set rng1 = Range(cell.Offset(1, -1), ar.Offset(-1, -1))
cell.Offset(0, 1).Value = Application.Sum(rng1)
End If
Set cell = ar
Next
End Sub

worked for me with your data. The sum is put adjacent to the top cell of
the boundary cells. (cells with Yes)


this assumes there are no adjacent cells that contain Yes.

--
Regards,
Tom Ogilvy



"Tre_cool" <[email protected]> wrote in
message news:[email protected]...
>
> Tom,
> It's the sum between the "Yes"'s, so =Sum(A2:A3). The formula
> to generate the "Yes" and 0 are in the cells in Column B.
>
>
> The results will just go in Column C. I hope that makes sense.
>
> Thanks
> Trevor
>
>
> --
> Tre_cool
> ------------------------------------------------------------------------
> Tre_cool's Profile:
http://www.excelforum.com/member.php...o&userid=26416
> View this thread: http://www.excelforum.com/showthread...hreadid=396852
>

[Tre_cool]

Thanks a lot Tom, it worked! One more thing... How can I find the sum for the last segment as well from lines 7 to 10? from the last yes to the eof?

1 3 Yes
2 6 0
3 8 0
4 11 Yes
5 12 0
6 16 Yes
7 14 0
8 3 0
9 13 0
10 2 0


Thanks.
Trevor

[Tre_cool]

Tom, this works!! However when there are multiple yes' it does not work, for example, in row 4 and 5. This is currently giving the same sum for both 4 and 5. In line 4, the sum should be 12 and for line 5, it should be 16+14+3.

1 3 Yes
2 6 0
3 8 0
4 11 Yes
5 12 Yes
6 16 0
7 14 0
8 3 0
9 13 Yes
10 2 0

Thanks in advance

Trevor