Sum Formula

[SRIKANTHBENONI]

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

 

[DavyJones]

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

 

[SRIKANTHBENONI]

Am using Excell 2010

 

[DavyJones]

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]

ADVERTISEMENT

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]

ADVERTISEMENT

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]

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]

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]

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