If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Summing values within a range
If I have a range of data values anywhere between 1 and 100, in an array
which covers cells A1:Z50, and I wish to sum all the values which fall between a particular range, say 50 and 55, how do I go about doing this? Similarly, how can I create a text string in the format {A1,B22,C19,C54...} etc which includes the cells containing values within my specified range? Is this wishful thinking? |
#2
|
|||
|
|||
=SUMPRODUCT(--(A1:Z50=50),--(A1:Z50=55),A1:Z50) VBA would be easier for the last bit Function Addresses(rng As Range) Dim cell As Range For Each cell In rng If cell.Value = 50 And cell.Value = 55 Then Addresses = Addresses & cell.Address(False, False) & "," End If Next cell Addresses = Left(Addresses, Len(Addresses) - 1) End Function used like =addresses(A1:z50) -- HTH RP (remove nothere from the email address if mailing direct) "rmellison" wrote in message ... If I have a range of data values anywhere between 1 and 100, in an array which covers cells A1:Z50, and I wish to sum all the values which fall between a particular range, say 50 and 55, how do I go about doing this? Similarly, how can I create a text string in the format {A1,B22,C19,C54...} etc which includes the cells containing values within my specified range? Is this wishful thinking? |
#3
|
|||
|
|||
"SUMPRODUCT" worked a treat, many thanks.
The VBA script is a little beyond me though, I have done next to nothing in VBA other than record a macro in Excel. I have written your suggested code in the editor and tried calling the function in a cell using =addresses(range), but I just get #NAME? in the cell. Is there something else I need to include in the VBA editor? Or in excel? Also, how would you modify the code to include two cell references as the upper and lower bounds of the range, such that you could call the function by writing =ADDRESSES(Range,lower,upper)?? Thanks in advance! "Bob Phillips" wrote: =SUMPRODUCT(--(A1:Z50=50),--(A1:Z50=55),A1:Z50) VBA would be easier for the last bit Function Addresses(rng As Range) Dim cell As Range For Each cell In rng If cell.Value = 50 And cell.Value = 55 Then Addresses = Addresses & cell.Address(False, False) & "," End If Next cell Addresses = Left(Addresses, Len(Addresses) - 1) End Function used like =addresses(A1:z50) -- HTH RP (remove nothere from the email address if mailing direct) "rmellison" wrote in message ... If I have a range of data values anywhere between 1 and 100, in an array which covers cells A1:Z50, and I wish to sum all the values which fall between a particular range, say 50 and 55, how do I go about doing this? Similarly, how can I create a text string in the format {A1,B22,C19,C54...} etc which includes the cells containing values within my specified range? Is this wishful thinking? |
#4
|
|||
|
|||
Not sure why it didn't work, but the #'NAME error suggest it cannot find the
function. You should store it in a standard code module (Alt-F11, menu InsertModule, copy the code in). Here is the revised version Function Addresses(rng As Range, upper, lower) Dim cell As Range For Each cell In rng If cell.Value = lower And cell.Value = upper Then Addresses = Addresses & cell.Address(False, False) & "," End If Next cell Addresses = Left(Addresses, Len(Addresses) - 1) End Function -- HTH RP (remove nothere from the email address if mailing direct) "rmellison" wrote in message ... "SUMPRODUCT" worked a treat, many thanks. The VBA script is a little beyond me though, I have done next to nothing in VBA other than record a macro in Excel. I have written your suggested code in the editor and tried calling the function in a cell using =addresses(range), but I just get #NAME? in the cell. Is there something else I need to include in the VBA editor? Or in excel? Also, how would you modify the code to include two cell references as the upper and lower bounds of the range, such that you could call the function by writing =ADDRESSES(Range,lower,upper)?? Thanks in advance! "Bob Phillips" wrote: =SUMPRODUCT(--(A1:Z50=50),--(A1:Z50=55),A1:Z50) VBA would be easier for the last bit Function Addresses(rng As Range) Dim cell As Range For Each cell In rng If cell.Value = 50 And cell.Value = 55 Then Addresses = Addresses & cell.Address(False, False) & "," End If Next cell Addresses = Left(Addresses, Len(Addresses) - 1) End Function used like =addresses(A1:z50) -- HTH RP (remove nothere from the email address if mailing direct) "rmellison" wrote in message ... If I have a range of data values anywhere between 1 and 100, in an array which covers cells A1:Z50, and I wish to sum all the values which fall between a particular range, say 50 and 55, how do I go about doing this? Similarly, how can I create a text string in the format {A1,B22,C19,C54...} etc which includes the cells containing values within my specified range? Is this wishful thinking? |
#5
|
|||
|
|||
It didn't work because I wrote the code in the wrong place; wrote it in
Microsfot Excel Objects | This Workbook, rather than as a module. Didn't I say I was a VBA novice! Have rectified the situation now with the new code in the right place, and it works well. However, I can't seem to use the resultant string as a refererence for use with other functions (such as MEDIAN, AVERAGE, STDEV). I've tried using INDIRECT(), i've modified the VB code to output a list of the cell values, i've even copied the cell values to an adjacent column to use that as a refence rather than the cell containing the original 'Addresses' function. All have proved to be fruitless! Clearly I'm trying the wrong things. Any further suggestions apprectiated. Many thanks for your assistance! "Bob Phillips" wrote: Not sure why it didn't work, but the #'NAME error suggest it cannot find the function. You should store it in a standard code module (Alt-F11, menu InsertModule, copy the code in). Here is the revised version Function Addresses(rng As Range, upper, lower) Dim cell As Range For Each cell In rng If cell.Value = lower And cell.Value = upper Then Addresses = Addresses & cell.Address(False, False) & "," End If Next cell Addresses = Left(Addresses, Len(Addresses) - 1) End Function -- HTH RP (remove nothere from the email address if mailing direct) "rmellison" wrote in message ... "SUMPRODUCT" worked a treat, many thanks. The VBA script is a little beyond me though, I have done next to nothing in VBA other than record a macro in Excel. I have written your suggested code in the editor and tried calling the function in a cell using =addresses(range), but I just get #NAME? in the cell. Is there something else I need to include in the VBA editor? Or in excel? Also, how would you modify the code to include two cell references as the upper and lower bounds of the range, such that you could call the function by writing =ADDRESSES(Range,lower,upper)?? Thanks in advance! "Bob Phillips" wrote: =SUMPRODUCT(--(A1:Z50=50),--(A1:Z50=55),A1:Z50) VBA would be easier for the last bit Function Addresses(rng As Range) Dim cell As Range For Each cell In rng If cell.Value = 50 And cell.Value = 55 Then Addresses = Addresses & cell.Address(False, False) & "," End If Next cell Addresses = Left(Addresses, Len(Addresses) - 1) End Function used like =addresses(A1:z50) -- HTH RP (remove nothere from the email address if mailing direct) "rmellison" wrote in message ... If I have a range of data values anywhere between 1 and 100, in an array which covers cells A1:Z50, and I wish to sum all the values which fall between a particular range, say 50 and 55, how do I go about doing this? Similarly, how can I create a text string in the format {A1,B22,C19,C54...} etc which includes the cells containing values within my specified range? Is this wishful thinking? |
#6
|
|||
|
|||
You asked the wrong question :-).
You asked for a function to get a string of cell addresses, which is exactly what you got. Those other functions require cell references, not address strings, the difference between say SUM(A1:A10) and SUM("A1:A10"). You don't need VBA for this, all you need is a formula, like so =SUM(IF((A1:C5=10)*(A1:C5=20),A1:C5)) which is an aray formula, so commit with Ctrl-Shift-Enter -- HTH RP (remove nothere from the email address if mailing direct) "rmellison" wrote in message news It didn't work because I wrote the code in the wrong place; wrote it in Microsfot Excel Objects | This Workbook, rather than as a module. Didn't I say I was a VBA novice! Have rectified the situation now with the new code in the right place, and it works well. However, I can't seem to use the resultant string as a refererence for use with other functions (such as MEDIAN, AVERAGE, STDEV). I've tried using INDIRECT(), i've modified the VB code to output a list of the cell values, i've even copied the cell values to an adjacent column to use that as a refence rather than the cell containing the original 'Addresses' function. All have proved to be fruitless! Clearly I'm trying the wrong things. Any further suggestions apprectiated. Many thanks for your assistance! "Bob Phillips" wrote: Not sure why it didn't work, but the #'NAME error suggest it cannot find the function. You should store it in a standard code module (Alt-F11, menu InsertModule, copy the code in). Here is the revised version Function Addresses(rng As Range, upper, lower) Dim cell As Range For Each cell In rng If cell.Value = lower And cell.Value = upper Then Addresses = Addresses & cell.Address(False, False) & "," End If Next cell Addresses = Left(Addresses, Len(Addresses) - 1) End Function -- HTH RP (remove nothere from the email address if mailing direct) "rmellison" wrote in message ... "SUMPRODUCT" worked a treat, many thanks. The VBA script is a little beyond me though, I have done next to nothing in VBA other than record a macro in Excel. I have written your suggested code in the editor and tried calling the function in a cell using =addresses(range), but I just get #NAME? in the cell. Is there something else I need to include in the VBA editor? Or in excel? Also, how would you modify the code to include two cell references as the upper and lower bounds of the range, such that you could call the function by writing =ADDRESSES(Range,lower,upper)?? Thanks in advance! "Bob Phillips" wrote: =SUMPRODUCT(--(A1:Z50=50),--(A1:Z50=55),A1:Z50) VBA would be easier for the last bit Function Addresses(rng As Range) Dim cell As Range For Each cell In rng If cell.Value = 50 And cell.Value = 55 Then Addresses = Addresses & cell.Address(False, False) & "," End If Next cell Addresses = Left(Addresses, Len(Addresses) - 1) End Function used like =addresses(A1:z50) -- HTH RP (remove nothere from the email address if mailing direct) "rmellison" wrote in message ... If I have a range of data values anywhere between 1 and 100, in an array which covers cells A1:Z50, and I wish to sum all the values which fall between a particular range, say 50 and 55, how do I go about doing this? Similarly, how can I create a text string in the format {A1,B22,C19,C54...} etc which includes the cells containing values within my specified range? Is this wishful thinking? |
#7
|
|||
|
|||
Got it! Works as required now. Thanks for all your help.
"Bob Phillips" wrote: You asked the wrong question :-). You asked for a function to get a string of cell addresses, which is exactly what you got. Those other functions require cell references, not address strings, the difference between say SUM(A1:A10) and SUM("A1:A10"). You don't need VBA for this, all you need is a formula, like so =SUM(IF((A1:C5=10)*(A1:C5=20),A1:C5)) which is an aray formula, so commit with Ctrl-Shift-Enter -- HTH RP (remove nothere from the email address if mailing direct) "rmellison" wrote in message news It didn't work because I wrote the code in the wrong place; wrote it in Microsfot Excel Objects | This Workbook, rather than as a module. Didn't I say I was a VBA novice! Have rectified the situation now with the new code in the right place, and it works well. However, I can't seem to use the resultant string as a refererence for use with other functions (such as MEDIAN, AVERAGE, STDEV). I've tried using INDIRECT(), i've modified the VB code to output a list of the cell values, i've even copied the cell values to an adjacent column to use that as a refence rather than the cell containing the original 'Addresses' function. All have proved to be fruitless! Clearly I'm trying the wrong things. Any further suggestions apprectiated. Many thanks for your assistance! "Bob Phillips" wrote: Not sure why it didn't work, but the #'NAME error suggest it cannot find the function. You should store it in a standard code module (Alt-F11, menu InsertModule, copy the code in). Here is the revised version Function Addresses(rng As Range, upper, lower) Dim cell As Range For Each cell In rng If cell.Value = lower And cell.Value = upper Then Addresses = Addresses & cell.Address(False, False) & "," End If Next cell Addresses = Left(Addresses, Len(Addresses) - 1) End Function -- HTH RP (remove nothere from the email address if mailing direct) "rmellison" wrote in message ... "SUMPRODUCT" worked a treat, many thanks. The VBA script is a little beyond me though, I have done next to nothing in VBA other than record a macro in Excel. I have written your suggested code in the editor and tried calling the function in a cell using =addresses(range), but I just get #NAME? in the cell. Is there something else I need to include in the VBA editor? Or in excel? Also, how would you modify the code to include two cell references as the upper and lower bounds of the range, such that you could call the function by writing =ADDRESSES(Range,lower,upper)?? Thanks in advance! "Bob Phillips" wrote: =SUMPRODUCT(--(A1:Z50=50),--(A1:Z50=55),A1:Z50) VBA would be easier for the last bit Function Addresses(rng As Range) Dim cell As Range For Each cell In rng If cell.Value = 50 And cell.Value = 55 Then Addresses = Addresses & cell.Address(False, False) & "," End If Next cell Addresses = Left(Addresses, Len(Addresses) - 1) End Function used like =addresses(A1:z50) -- HTH RP (remove nothere from the email address if mailing direct) "rmellison" wrote in message ... If I have a range of data values anywhere between 1 and 100, in an array which covers cells A1:Z50, and I wish to sum all the values which fall between a particular range, say 50 and 55, how do I go about doing this? Similarly, how can I create a text string in the format {A1,B22,C19,C54...} etc which includes the cells containing values within my specified range? Is this wishful thinking? |
#8
|
|||
|
|||
Great. Glad we got there.
Bob "rmellison" wrote in message ... Got it! Works as required now. Thanks for all your help. "Bob Phillips" wrote: You asked the wrong question :-). You asked for a function to get a string of cell addresses, which is exactly what you got. Those other functions require cell references, not address strings, the difference between say SUM(A1:A10) and SUM("A1:A10"). You don't need VBA for this, all you need is a formula, like so =SUM(IF((A1:C5=10)*(A1:C5=20),A1:C5)) which is an aray formula, so commit with Ctrl-Shift-Enter -- HTH RP (remove nothere from the email address if mailing direct) "rmellison" wrote in message news It didn't work because I wrote the code in the wrong place; wrote it in Microsfot Excel Objects | This Workbook, rather than as a module. Didn't I say I was a VBA novice! Have rectified the situation now with the new code in the right place, and it works well. However, I can't seem to use the resultant string as a refererence for use with other functions (such as MEDIAN, AVERAGE, STDEV). I've tried using INDIRECT(), i've modified the VB code to output a list of the cell values, i've even copied the cell values to an adjacent column to use that as a refence rather than the cell containing the original 'Addresses' function. All have proved to be fruitless! Clearly I'm trying the wrong things. Any further suggestions apprectiated. Many thanks for your assistance! "Bob Phillips" wrote: Not sure why it didn't work, but the #'NAME error suggest it cannot find the function. You should store it in a standard code module (Alt-F11, menu InsertModule, copy the code in). Here is the revised version Function Addresses(rng As Range, upper, lower) Dim cell As Range For Each cell In rng If cell.Value = lower And cell.Value = upper Then Addresses = Addresses & cell.Address(False, False) & "," End If Next cell Addresses = Left(Addresses, Len(Addresses) - 1) End Function -- HTH RP (remove nothere from the email address if mailing direct) "rmellison" wrote in message ... "SUMPRODUCT" worked a treat, many thanks. The VBA script is a little beyond me though, I have done next to nothing in VBA other than record a macro in Excel. I have written your suggested code in the editor and tried calling the function in a cell using =addresses(range), but I just get #NAME? in the cell. Is there something else I need to include in the VBA editor? Or in excel? Also, how would you modify the code to include two cell references as the upper and lower bounds of the range, such that you could call the function by writing =ADDRESSES(Range,lower,upper)?? Thanks in advance! "Bob Phillips" wrote: =SUMPRODUCT(--(A1:Z50=50),--(A1:Z50=55),A1:Z50) VBA would be easier for the last bit Function Addresses(rng As Range) Dim cell As Range For Each cell In rng If cell.Value = 50 And cell.Value = 55 Then Addresses = Addresses & cell.Address(False, False) & "," End If Next cell Addresses = Left(Addresses, Len(Addresses) - 1) End Function used like =addresses(A1:z50) -- HTH RP (remove nothere from the email address if mailing direct) "rmellison" wrote in message ... If I have a range of data values anywhere between 1 and 100, in an array which covers cells A1:Z50, and I wish to sum all the values which fall between a particular range, say 50 and 55, how do I go about doing this? Similarly, how can I create a text string in the format {A1,B22,C19,C54...} etc which includes the cells containing values within my specified range? Is this wishful thinking? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How to get 'top 10' text values from a range | henryhbruce | Worksheet Functions | 1 | April 25th, 2005 05:00 PM |
Formula to Count and Return Most common Value in a Dynamic Named Range | Tinä | General Discussion | 1 | October 23rd, 2004 08:51 PM |
Summing values with multiple criteria | Peter | Worksheet Functions | 1 | July 22nd, 2004 07:02 PM |
Question about an argument, in an OFFSET dynamic range formula | Terry B. | Worksheet Functions | 6 | December 10th, 2003 10:53 PM |
sum top values in a range | Jonathan Parminter | Worksheet Functions | 4 | December 9th, 2003 03:18 AM |