XL2002 SUM with a twist...

  • Thread starter Trevor Williams
  • Start date
T

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
 
L

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

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!.
 
T

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top