Summing values within a range

R

rmellison

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

Bob Phillips

=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)
 
R

rmellison

"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!
 
B

Bob Phillips

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)
 
R

rmellison

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!
 
B

Bob Phillips

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)
 
Top