Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Searching and finding | Excel Worksheet Functions | |||
Finding, searching, and comparing cells to another sheet | Excel Worksheet Functions | |||
Searching for a value in an Array | Excel Worksheet Functions | |||
searching in an array | Excel Discussion (Misc queries) | |||
Searching an Array | Excel Programming |