Calculate sum of top half rows in a column

[tigertime]

Does any one know the formula to calculate top half rows/ bottom half rows of column D,
Simply by selecting ALL the rows in column D, as shown in the image

(I am not looking for the =SUM formula )

i have several tables like this and sometimes the number of rows may be odd/even

 

[LloydFinancials]

Adjust the 500 as needed, but for the top half: =SUM(INDIRECT("D1:D"&ROUNDUP(COUNTA(D1:D500)/2,0)))

Bottom half: =SUM(INDIRECT("D"&ROUNDUP(COUNTA(D1:D500)/2,0)+1&":D"&COUNTA(D1:D500)))

These formulas put the odd number row in the top half.

 

[tigertime]

Adjust the 500 as needed, but for the top half: =SUM(INDIRECT("D1:D"&ROUNDUP(COUNTA(D1:D500)/2,0)))

Bottom half: =SUM(INDIRECT("D"&ROUNDUP(COUNTA(D1:D500)/2,0)+1&":D"&COUNTA(D1:D500)))

These formulas put the odd number row in the top half.

i adjusted (D1:D500) to (D1:D39) at the above 3 areas, it did not sum up correct

 

[LloydFinancials]

I get 330000 for the top half D1:D20 and 388900 for the bottom half D21:D39 with those formulas.

 

[Dave Patton]

The suggested solution should work. I included that solution with my mini example.
You can review the following.

Excel 2010
	A	B	C	D	E	F	G
1		12	12	2	2	1	12
2		600	600	4	4	3	600
3				6	6
4		112	1500	100	100	6	112
5				200	200	8	1500
6		112	1500	300	300
7					1000
5a

Cell Formulas
Range	Formula
B1	=SUM(INDIRECT("D" &F1& ":D" &F2))
B2	=SUM(INDIRECT("D" &F2+1& ":D" &F4))
B4	=SUM(INDIRECT("E" &F1& ":E" &F5/2))
B6	=SUM(INDIRECT("E1" & ":E" &(ISODD(COUNT(E:E))+COUNT(E:E))/2))
C1	=SUM(INDIRECT("D1" & ":D" &COUNT(D1:D500)/2))
C2	=SUM(INDIRECT("D" &COUNT(D:D)/2+1& ":D" &COUNT(D:D)))
C4	=SUM(INDIRECT("E" &F5/2+1& ":E" &F5))
C6	=SUM(E:E)-B6
G1	=SUM(INDIRECT("D1:D"&ROUNDUP(COUNTA(D1:D500)/2,0)))
G2	=SUM(INDIRECT("D"&ROUNDUP(COUNTA(D1:D500)/2,0)+1&":D"&COUNTA(D1:D500)))
G4	=SUM(INDIRECT("E1:E"&ROUNDUP(COUNTA(E1:E500)/2,0)))
G5	=SUM(INDIRECT("E"&ROUNDUP(COUNTA(E1:E500)/2,0)+1&":E"&COUNTA(E1:E500)))
F2	=COUNT(D:D)/2
F4	=ISODD(COUNT(D:D))+COUNT(D:D)
F5	=ISODD(COUNT(E:E))+COUNT(E:E)

 

[tigertime]

Thanks for your reply but it is too compllicated , i do not quite understand,
i only understood the part where you mentioned that his solutionworks, so i went to recheck my calculations again...

The suggested solution should work. I included that solution with my mini example.
You can review the following.

Excel 2010

	A	B	C	D	E	F	G

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D8E4BC]#D8E4BC[/URL] , align: right"]12[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D8E4BC]#D8E4BC[/URL] , align: right"]12[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]12[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D8E4BC]#D8E4BC[/URL] , align: right"]600[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D8E4BC]#D8E4BC[/URL] , align: right"]600[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]600[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D8E4BC]#D8E4BC[/URL] , align: right"]112[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D8E4BC]#D8E4BC[/URL] , align: right"]1500[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]112[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]1500[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D8E4BC]#D8E4BC[/URL] , align: right"]112[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D8E4BC]#D8E4BC[/URL] , align: right"]1500[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

5a​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=SUM(INDIRECT("D" &F1& ":D" &F2))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C1[/TH]
[TD="align: left"]=SUM(INDIRECT("D1" & ":D" &COUNT(D1:D500)/2))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=SUM(INDIRECT("D" &F2+1& ":D" &F4))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=SUM(INDIRECT("D" &COUNT(D:D)/2+1& ":D" &COUNT(D:D)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G1[/TH]
[TD="align: left"]=SUM(INDIRECT("D1:D"&ROUNDUP(COUNTA(D1:D500)/2,0)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=COUNT(D:D)/2[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]=SUM(INDIRECT("D"&ROUNDUP(COUNTA(D1:D500)/2,0)+1&":D"&COUNTA(D1:D500)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B4[/TH]
[TD="align: left"]=SUM(INDIRECT("E" &F1& ":E" &F5/2))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C4[/TH]
[TD="align: left"]=SUM(INDIRECT("E" &F5/2+1& ":E" &F5))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F4[/TH]
[TD="align: left"]=ISODD(COUNT(D:D))+COUNT(D:D)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G4[/TH]
[TD="align: left"]=SUM(INDIRECT("E1:E"&ROUNDUP(COUNTA(E1:E500)/2,0)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F5[/TH]
[TD="align: left"]=ISODD(COUNT(E:E))+COUNT(E:E)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G5[/TH]
[TD="align: left"]=SUM(INDIRECT("E"&ROUNDUP(COUNTA(E1:E500)/2,0)+1&":E"&COUNTA(E1:E500)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B6[/TH]
[TD="align: left"]=SUM(INDIRECT("E1" & ":E" &(ISODD(COUNT(E:E))+COUNT(E:E))/2))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C6[/TH]
[TD="align: left"]=SUM(E:E)-B6[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[tigertime]

I get 330000 for the top half D1:D20 and 388900 for the bottom half D21:D39 with those formulas.

This is what i got from manual calculation:

Top half
Row 1 to Row 19 (19 rows)
i got 304500

Row 21 to Row 39 (also 19 rows)
Bottom half i got 388900

The middle cell at Row 20 is 25500, how to divide by 2 and add it to the top and bottom half using your formula ?

 

[Dave Patton]

I do not know if you reviewed any of the suggestions in my post above.

Since you appear to want to split the median number, try the following

=SUM(INDIRECT("E1"&":E"&INT(COUNT(E:E)/2)))+ISODD(COUNT(E:E))*MEDIAN(E:E)/2

On my sheet the formula for the first half is in B8

Last half is =SUM(E:E)-B8

 

[tigertime]

I do not know if you reviewed any of the suggestions in my post above.

Since you appear to want to split the median number, try the following

=SUM(INDIRECT("E1"&":E"&INT(COUNT(E:E)/2)))+ISODD(COUNT(E:E))*MEDIAN(E:E)/2

On my sheet the formula for the first half is in B8

Last half is =SUM(E:E)-B8

i am unsure which areas i should select in your formula to replace for my columns... i am using column D, but there are so many E in your formula, i replaced all the E and it didnt work

 

[Dave Patton]

Try testing with the mini example.

Excel 2010
	A	B	C	D	E
1				2	2
2				4	4
3				6	6
4				100	100
5				200	200
6		First 1/2	Second 1/2	300	300
7	Column D	12	600		1000
8	Column E	62	1550
5aa

Cell Formulas
Range	Formula
B7	=SUM(INDIRECT("D1"&":D"&INT(COUNT(D:D)/2)))+ISODD(COUNT(D:D))*MEDIAN(D:D)/2
B8	=SUM(INDIRECT("E1"&":E"&INT(COUNT(E:E)/2)))+ISODD(COUNT(E:E))*MEDIAN(E:E)/2
C7	=SUM(D:D)-B7
C8	=SUM(E:E)-B8