Find the sum of values in column D that equals the value
in A1
D
A1 10
600 100
500
50
200
Is there a function that does this and can it tell me
which cells in Column D were used to find the Value in A1

Also if multiple if source values are used.

Complicated I know but no other way of explaining.
Currently using a macro to find if cell and cell.offset
(1,0)=A1 etc


Send the answer to as well as
here.

Probably no result, that's okay.

"Mark" skrev i melding
...
Find the sum of values in column D that equals the value
in A1
D
A1 10
600 100
500
50
200

=SUMIF(D1:D1000,A1)
this used in VBA:
MsgBox Application.WorksheetFunction.SumIf(Range(Cells(1, 4), Cells(1000,
4)), Cells(1, 1))

Is there a function that does this and can it tell me
which cells in Column D were used to find the Value in A1

I'd use a loop for that:

Private Sub CommandButton1_Click()
Dim R As Range, Cel As Range
Dim S As String
Set R = Range(Cells(1, 4), Cells(1000, 4))
For Each Cel In R
If Cel.Value = Cells(1, 1).Value Then _
S = S & Cel.Address(0, 0, xlA1) & ", "
Next
MsgBox S
End Sub

HTH. Best wishes Harald

Thanks Harald for the reply. I did not phrase it too well and we, who
reply to others, always complain of obtuse or lack of information of
requesters.

Like they say, a doctor is the worst patient.

Anyway, i will try out your reply on the data.

Thanks again
mark


*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!