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?

=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?

"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?

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?

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?

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
...
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?

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
...
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?

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
...
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?