Sum help please...

[boof bonk boosh]

I have searched and searched but cannot find anything to assist, so I decided to start my own thread. I hope this is OK.

I need to sum the total of the first 3 populated cells from 5.

ie.

A1 3.5
A2 3.1
A3 2.6
A4 1.9
A5 5

I need A1+A2+A3. However it may not always be so simple, as some cells may be blank.

ie.

A1 3.5
A2
A3 2.6
A4 1.9
A5 5

I need A1+A3+A4.

Is it possible to sum UNTIL 3 cells have been summed? Will I need to work along a row instead of down a column?

Any help would be greatly appreciated.

[mrice]

Try this...

=A1+A2+A3+IF(COUNT(A1:A3)<3,A4,0) +IF(COUNT(A1:A4)<3,A5,0)

The values of A4 and A5 are only included if there are less than three numbers in the cells above them.

Hope this helps.

[boof bonk boosh]

Try this...

=A1+A2+A3+IF(COUNT(A1:A3)<3,A4,0) +IF(COUNT(A1:A4)<3,A5,0)

The values of A4 and A5 are only included if there are less than three numbers in the cells above them.

Hope this helps.

A great help, many thanks. Quite straight forward when it is spelt out for you, but I'd been pulling my hair out for hours...!!!

[roadkill]

It's a little brute force, but how about something like:

"=IF(COUNTA(A1:A3)=3,SUM(A1:A3),IF(COUNTA(A1:A4)=3,SUM(A1:A4),SUM(A1:A5)))"

If there aren't 3 numbers in the 5 this will give you the sum of what's
there (or you can modify it to do something else).

Will

"boof bonk boosh" wrote:

>
> I have searched and searched but cannot find anything to assist, so I
> decided to start my own thread. I hope this is OK.
>
> I need to sum the total of the first 3 populated cells from 5.
>
> ie.
>
> A1 3.5
> A2 3.1
> A3 2.6
> A4 1.9
> A5 5
>
> I need A1+A2+A3. However it may not always be so simple, as some cells
> may be blank.
>
> ie.
>
> A1 3.5
> A2
> A3 2.6
> A4 1.9
> A5 5
>
> I need A1+A3+A4.
>
> Is it possible to sum UNTIL 3 cells have been summed? Will I need to
> work along a row instead of down a column?
>
> Any help would be greatly appreciated.
>
>
> --
> boof bonk boosh
> ------------------------------------------------------------------------
> boof bonk boosh's Profile: http://www.excelforum.com/member.php...o&userid=35202
> View this thread: http://www.excelforum.com/showthread...hreadid=549670
>
>

[Kevin B]

The following UDF will sum the first 3 numbers it finds in a range of cells.
Press Alt+F11 to open the VBE, click INSERT in the menu and click MODULE.
You can paste the code in as is:

Function SumThree(CellRange As Range) As Double

Dim r As Range
Dim i As Integer
Dim intCounter As Integer
Dim varVal As Variant
Dim dblSum As Double

Set r = CellRange
Application.Volatile

For i = 1 To r.Cells.Count
varVal = r.Cells(i)
If varVal <> "" Then
If IsNumeric(varVal) Then
dblSum = dblSum + CDbl(varVal)
intCounter = intCounter + 1
If intCounter = 3 Then Exit For
End If
End If
Next i

Set r = Nothing
SumThree = dblSum
Exit Function

End Function

--
Kevin Backmann


"boof bonk boosh" wrote:

>
> I have searched and searched but cannot find anything to assist, so I
> decided to start my own thread. I hope this is OK.
>
> I need to sum the total of the first 3 populated cells from 5.
>
> ie.
>
> A1 3.5
> A2 3.1
> A3 2.6
> A4 1.9
> A5 5
>
> I need A1+A2+A3. However it may not always be so simple, as some cells
> may be blank.
>
> ie.
>
> A1 3.5
> A2
> A3 2.6
> A4 1.9
> A5 5
>
> I need A1+A3+A4.
>
> Is it possible to sum UNTIL 3 cells have been summed? Will I need to
> work along a row instead of down a column?
>
> Any help would be greatly appreciated.
>
>
> --
> boof bonk boosh
> ------------------------------------------------------------------------
> boof bonk boosh's Profile: http://www.excelforum.com/member.php...o&userid=35202
> View this thread: http://www.excelforum.com/showthread...hreadid=549670
>
>

[boof bonk boosh]

Thanks very much guys. You've been a great help..!!!

[Don Guillett]

try this ARRAY formula which must be entered using crtl+shift+enter
=SUM(OFFSET($B$2,0,0,SMALL(IF(B2:B15<>"",ROW(B2:B15)),3)-1,1))

--
Don Guillett
SalesAid Software
[email protected]
"boof bonk boosh"
<[email protected]> wrote in
message news:[email protected]...
>
> I have searched and searched but cannot find anything to assist, so I
> decided to start my own thread. I hope this is OK.
>
> I need to sum the total of the first 3 populated cells from 5.
>
> ie.
>
> A1 3.5
> A2 3.1
> A3 2.6
> A4 1.9
> A5 5
>
> I need A1+A2+A3. However it may not always be so simple, as some cells
> may be blank.
>
> ie.
>
> A1 3.5
> A2
> A3 2.6
> A4 1.9
> A5 5
>
> I need A1+A3+A4.
>
> Is it possible to sum UNTIL 3 cells have been summed? Will I need to
> work along a row instead of down a column?
>
> Any help would be greatly appreciated.
>
>
> --
> boof bonk boosh
> ------------------------------------------------------------------------
> boof bonk boosh's Profile:
> http://www.excelforum.com/member.php...o&userid=35202
> View this thread: http://www.excelforum.com/showthread...hreadid=549670
>

[Don Guillett]

change to this. Again, this is an array formula
=SUM(OFFSET($B$2,0,0,SMALL(IF(ISNUMBER($B$2:$B$15),ROW($B$2:$B$15)),3)-1,1))

--
Don Guillett
SalesAid Software
[email protected]
"Don Guillett" <[email protected]> wrote in message
news:[email protected]...
> try this ARRAY formula which must be entered using crtl+shift+enter
> =SUM(OFFSET($B$2,0,0,SMALL(IF(B2:B15<>"",ROW(B2:B15)),3)-1,1))
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "boof bonk boosh"
> <[email protected]> wrote in
> message
> news:[email protected]...
>>
>> I have searched and searched but cannot find anything to assist, so I
>> decided to start my own thread. I hope this is OK.
>>
>> I need to sum the total of the first 3 populated cells from 5.
>>
>> ie.
>>
>> A1 3.5
>> A2 3.1
>> A3 2.6
>> A4 1.9
>> A5 5
>>
>> I need A1+A2+A3. However it may not always be so simple, as some cells
>> may be blank.
>>
>> ie.
>>
>> A1 3.5
>> A2
>> A3 2.6
>> A4 1.9
>> A5 5
>>
>> I need A1+A3+A4.
>>
>> Is it possible to sum UNTIL 3 cells have been summed? Will I need to
>> work along a row instead of down a column?
>>
>> Any help would be greatly appreciated.
>>
>>
>> --
>> boof bonk boosh
>> ------------------------------------------------------------------------
>> boof bonk boosh's Profile:
>> http://www.excelforum.com/member.php...o&userid=35202
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=549670
>>
>
>