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" <rmellison@discussions.microsoft.com> wrote in message
news:CEF3471B-5072-4C6C-9FE0-A8F738DC6209@microsoft.com...
> 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" <rmellison@discussions.microsoft.com> wrote in message
> news:CEF3471B-5072-4C6C-9FE0-A8F738DC6209@microsoft.com...
> > 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
Insert>Module, 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" <rmellison@discussions.microsoft.com> wrote in message
news:07B37B62-FB9E-4B78-9309-2BEA21CF7791@microsoft.com...
> "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" <rmellison@discussions.microsoft.com> wrote in message
> > news:CEF3471B-5072-4C6C-9FE0-A8F738DC6209@microsoft.com...
> > > 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
> Insert>Module, 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" <rmellison@discussions.microsoft.com> wrote in message
> news:07B37B62-FB9E-4B78-9309-2BEA21CF7791@microsoft.com...
> > "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" <rmellison@discussions.microsoft.com> wrote in message
> > > news:CEF3471B-5072-4C6C-9FE0-A8F738DC6209@microsoft.com...
> > > > 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" <rmellison@discussions.microsoft.com> wrote in message
news:DDA170A9-3D80-400C-A8AF-670FD3C3B7CA@microsoft.com...
> 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
> > Insert>Module, 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" <rmellison@discussions.microsoft.com> wrote in message
> > news:07B37B62-FB9E-4B78-9309-2BEA21CF7791@microsoft.com...
> > > "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" <rmellison@discussions.microsoft.com> wrote in message
> > > > news:CEF3471B-5072-4C6C-9FE0-A8F738DC6209@microsoft.com...
> > > > > 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" <rmellison@discussions.microsoft.com> wrote in message
> news:DDA170A9-3D80-400C-A8AF-670FD3C3B7CA@microsoft.com...
> > 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
> > > Insert>Module, 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" <rmellison@discussions.microsoft.com> wrote in message
> > > news:07B37B62-FB9E-4B78-9309-2BEA21CF7791@microsoft.com...
> > > > "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" <rmellison@discussions.microsoft.com> wrote in message
> > > > > news:CEF3471B-5072-4C6C-9FE0-A8F738DC6209@microsoft.com...
> > > > > > 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" <rmellison@discussions.microsoft.com> wrote in message
news:AA9ABE9A-F6F6-4A34-8B93-BCB0ECA0597B@microsoft.com...
> 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" <rmellison@discussions.microsoft.com> wrote in message
> > news:DDA170A9-3D80-400C-A8AF-670FD3C3B7CA@microsoft.com...
> > > 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
> > > > Insert>Module, 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" <rmellison@discussions.microsoft.com> wrote in message
> > > > news:07B37B62-FB9E-4B78-9309-2BEA21CF7791@microsoft.com...
> > > > > "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" <rmellison@discussions.microsoft.com> wrote in
message
> > > > > > news:CEF3471B-5072-4C6C-9FE0-A8F738DC6209@microsoft.com...
> > > > > > > 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?
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >