| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum help please...
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum help please...
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. -- mrice Research Scientist with many years of spreadsheet development experience ------------------------------------------------------------------------ mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931 View this thread: http://www.excelforum.com/showthread...hreadid=549670 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum help please...
mrice Wrote: 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...!!! -- 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum help please...
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum help please...
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum help please...
Thanks very much guys. You've been a great help..!!! -- 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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum help please...
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 "Don Guillett" wrote in message ... try this ARRAY formula which must be entered using crtl+shift+enter =SUM(OFFSET($B$2,0,0,SMALL(IF(B2:B15<"",ROW(B2:B1 5)),3)-1,1)) -- Don Guillett SalesAid Software "boof bonk boosh" <boof.bonk.boosh.291t2m_1149711001.5001@excelfor um-nospam.com wrote in message news:boof.bonk.boosh.291t2m_1149711001.5001@excelf orum-nospam.com... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
