Hi Luke -- works a treat. Thak you very much.
Trevor

"Luke M" wrote:

You can use this UDF:

'=========
Public Function SpecialSum(r As Range, c As Double) As Double
Value = 0

For Each cell In r
If c 0 Then
If cell.Value 0 Then
'Add to total
Value = Value + cell.Value
'Deduct from count of cells to look for
c = c - 1
End If
End If
Next cell

SpecialSum = Value
End Function
'=========

The formula in your workbook would then be:
=SpecialSum(A2:F2,A1)


To install a UDF:
Press Alt+F11 to bring up the VBE (Visual Basic Editor). Goto Insert -
Module. Paste the coding in. Close the VBE.

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Trevor Williams" wrote:

Hi All,

Is there a way to SUM a variable amount of values 0 where the number of
values to SUM is in another cell? -- let me try and explain a bit better.

Cell A1 has a value of 3 (this is how many values I need to SUM from the data)

Cells A2:F2 contains the data series - BUT
Cells B2 & D2 contain a zero

So, I need to SUM A2, C2, E2 (first 3 values greater than 0)

Also, if there are only 2 values 0 in the data series the SUM returns the
total those 2 values (without error) and ignores the fact it's looking for
3...

Hope that makes sense(?)

Look forward to your response.
Regards
Trevor Williams