XL2002 SUM with a twist...

[Trevor Williams]

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

 

[Luke M]

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.

 

[T. Valko]

Try this array formula** :

=SUM(A2:INDEX(A2:F2,SMALL(IF(A2:F2>0,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!.

 

[Trevor Williams]

Hi Luke - thanks for your response.
I
ve actually developed a bit of code to do the calcs, but it takes a fair bit
of time to cycle through 000's of lines of data.

I'll give your Function a go and report back -- hopefully it's a lot quicker!

Trevor

 

[Trevor Williams]

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