Searching an array and finding sum
 

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

Searching an array and finding sum
 

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" wrote in
message ...

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

Searching an array and finding sum
 

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

Searching an array and finding sum
 

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" wrote in
message ...

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

Searching an array and finding sum
 

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
------------------------------------------------------------------------
Tre_cool's Profile: http://www.excelforum.com/member.php...o&userid=26416
View this thread: http://www.excelforum.com/showthread...hreadid=396852

Searching an array and finding sum
 

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 fo
both 4 and 5.

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

Trevo

--
Tre_coo
-----------------------------------------------------------------------
Tre_cool's Profile: http://www.excelforum.com/member.php...fo&userid=2641
View this thread: http://www.excelforum.com/showthread.php?threadid=39685