Try this array formula** :

=SUM(A2:INDEX(A2:F2,SMALL(IF(A2:F20,COLUMN(A2:F2) ),MIN(A1,COUNTIF(A2:F2,"0")))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

If A1 is an empty cell the formula will calculate the entire range.

If there are no values 0 the formula will return the error #NUM!.

--
Biff
Microsoft Excel MVP


"Trevor Williams" wrote in
message ...
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