Sum Formula

SRIKANTHBENONI

New Member
Hi All,

I need help for the below Table, i have one Table which has GL and Amount and i need sum of amounts for each GL and Sum if amount is less than Zero or Greater Than Zero

Table:

<TABLE style="WIDTH: 163pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=217 border=0><COLGROUP><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 107pt; mso-width-source: userset; mso-width-alt: 5193" width=142><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff 0.5pt solid; BORDER-LEFT: #ccccff 0.5pt solid; WIDTH: 56pt; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #666699" width=75 height=20>GL</TD><TD class=xl63 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff 0.5pt solid; BORDER-LEFT: #ccccff; WIDTH: 107pt; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: #666699" width=142>Amount</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20>100070010</TD><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white">-5001.76</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20>100070010</TD><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white">-129456.60</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20>100070010</TD><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white">342620.27</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20>100070010</TD><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white">129456.60</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20>901001011</TD><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white">250.78</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20>901001011</TD><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white">-17178.22</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20>901001011</TD><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white">3093.06</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20>901001011</TD><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white">896.80</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20>901001011</TD><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white">6495.84</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20>901001011</TD><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white">-211870.91</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20>901001011</TD><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white">-61427.35</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20>901001011</TD><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white">-444963.52</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20>901031019</TD><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white">2329.28</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff 0.5pt solid; BORDER-BOTTOM: #ccccff 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20>901031019</TD><TD class=xl65 style="BORDER-RIGHT: #ccccff 0.5pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; BORDER-BOTTOM: #ccccff 0.5pt solid; BACKGROUND-COLOR: white">-159560.00</TD></TR></TBODY></TABLE>

Example:
I need to get following from above for GL 100070010

Sum for 100070010 is = 337618.51

Sum if less than Zero 100070010 is = -134458.36

Sum if Greater than Zero 100070010 is = 472076.87

Can someone help me in providing the Formula or the MAcro

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

DavyJones

Board Regular
You can do it with formulas, but which version of Excel are you using?

SRIKANTHBENONI

New Member
Am using Excell 2010

DavyJones

Board Regular
Try this to get you started, it will work in any version of Excel and calculates values less than zero where GL = 100070010, assuming your table is in the top left corner of your sheet, and you have entered the GL number you want in A18.

=SUM(IF((\$A\$2:\$A\$15=\$A18)*(\$B\$2:\$B\$15<0),\$B\$2:\$B\$15))

This is an array formula, so you need to press shift+control+enter to enter it.

If you're using excel 2007 onwards, you can use the SUMIFS function.

SRIKANTHBENONI

New Member
Am getting following Error

<TABLE style="WIDTH: 53pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=70 border=0><COLGROUP><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2560" width=70><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 53pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 30pt; BACKGROUND-COLOR: transparent" align=middle width=70 height=40 rowSpan=2>#VALUE!

</TD></TR><TR style="HEIGHT: 15pt" height=20></TR></TBODY></TABLE>

Joe4

Take a look at the SUMIFS function. Excel's help files should have some good details and examples.

Take a run at it, and if you cannot get it to work out, post your formula.

DavyJones

Board Regular
Try this one, assuming your data is in the same place and GL number in A18 as before

=SUMIFS(\$B\$2:\$B\$15,\$A\$2:\$A\$15,A18,B2:B15,"<0")

SRIKANTHBENONI

New Member
Thats really good, i have tried but no use..facing problem..

Formula Used:

=SUMIFS(B:B,A:A,"=A2",B:B,"<0")

Result : "0"

DavyJones

Board Regular
Thats really good, i have tried but no use..facing problem..

Formula Used:

=SUMIFS(B:B,A:A,"=A2",B:B,"<0")

Result : "0"

All you should need to do is remove your double quotes and = around A2 - because it's a cell reference, you don't need them, so your formula should read;
=SUMIFS(B:B,A:A,A2,B:B,"<0")

HTH

SRIKANTHBENONI

New Member
=SUMIFS(\$B\$2:\$B\$15,\$A\$2:\$A\$15,A18,B2:B15,"<0")
This formula is working...Greate!!! and Thanks alot for support

Replies
2
Views
419
Replies
0
Views
269
Replies
0
Views
91
Replies
1
Views
134
Replies
1
Views
233

1,195,970
Messages
6,012,609
Members
441,715
Latest member
TTP

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back