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