Sumif formula to generate trial balance from general ledger

[edmundmckay]

Hello

I have downloaded a General ledger and I am trying to form a manual trial balance of the chart codes within it using a couple of criteria. My quick solution to creating a trial balance is to generate a pivot table and utilise the chart code column and amount column and then report filter by the account date . My colleague would prefer to use sumif formula’s to generate the balance per chart code as there is specific criteria which potentially is needed to source down the line.

In order to achieve the below result from the pivot table using sumif formula I have had to quickly extract a list of all the codes being utilised on the ledger and use this list as the control list of codes to form the Trial Balance before writing the formula.

There are two tabs. The GL tab for data range and Trial balance Summary tab which is where I am building the Trial balance on

Here is my below SUMIF formula which brings the balance of the code within the GL

=SUMIF(GL!$F$2:$F$19727,'Trial Balance Summary'!A2,GL!$L$2:$L$19727)

I now would like to generate a trial balance which pulls through all figures which occurred on the 29/8/2017 and prior against each code

I tried a SUMIFS formula below but didn’t get any where.

SUMIFS(GL!$F$2:$F$19727,"<=","29/8/2017",GL!$A$19693:$A$19727,GL!$L$19693:$L$19727)

Please could you help and provide a working formula

Column reference for the data range used in formula. There are 19,727 rows within the GL
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Column A
[/TD]
[TD]Column F
[/TD]
[TD]Column L
[/TD]
[/TR]
[TR]
[TD]Account Date
[/TD]
[TD]
[TABLE="width: 70"]
<tbody>[TR]
[TD]chart_code
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 81"]
<tbody>[TR]
[TD] amount
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 111"]
<tbody>[TR]
[TD="align: right"]31/03/2008
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 70"]
<tbody>[TR]
[TD="align: right"]1200
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 81"]
<tbody>[TR]
[TD] 749.25
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


many thanks in advance

 

[Chrisdontm]

The SUMIFS formula has to be setup different than a regular SUMIF formula.

Notice the screen tip as you create the formula.

With SUMIF; you put a range first, then after the comma, you put the criteria to filter through that range.
[ Then if you want it to actually sum a different range you can add that in the next part. ]
( If there is not another range inserted then it will sum from the first range. )

With SUMIFS; You must list the range first that you want summed.
Then you enter two things at a time from then on, first a range, then a criteria, second a range, then the second criteria...etc....

Your SUMIFS has a range, then a criteria, then another criteria, then another range, and another range.
SUMIFS(GL!$F$2:$F$19727,"<=","29/8/2017",GL!$A$19693:$A$19727,GL!$L$19693:$L$19727)

Rearranging that could help.

One other thing that might become a problem is the formatting of the date.
Even if you set your dates up d/m/yyyy, when using the date in a formula Excel wants it in order of m/d/yyyy.
I don't know if there is a setting that could change that, but that is how I always have to do it.

So after listing a range to filter through, the criteria for the date would look like this: Range, "<=8/29/2017", Next_range, etc...

 

[kvsrinivasamurthy]

This works

=SUMIF(GL!$A$2:$A$19727,"<="&DATEVALUE("29/8/2017"),GL!$L$2:$L$19727)

 

[edmundmckay]

Hi KVSRINIVASAMURTHY and CHRISDONTN

Thank you both for responding quickly with my query. I appreciate the support and effort you have given and have sorted the formula to work.

I have utilised both your formula's and worked them into a table where a list of all the chart codes are listed from the GL and then I applied the below formula per code.

=SUMIFS(GL!$L$2:$L$19727,GL!$A$2:$A$19727,"<="&DATEVALUE("29/08/2017"),GL!$F$2:$F$19727,'Trial Balance Summary'!G2)

The last criteria "G2" is the first of the codes to formulate a balance against in my table which starts in column G.


Would either of you know how to make a control cell for this table of results in which you can type different dates into which updates the date section in the forumulas?


many thanks again

 

[Chrisdontm]

This would be how you can change the date at will;

Let us say that the date which you want to insert is in cell: H2 on the 'Trial Balance Summary'! tab.

Remove what was there: DATEVALUE("29/08/2017") and insert the cell reference...

=SUMIFS(GL!$L$2:$L$19727,GL!$A$2:$A$19727,"<="&'Trial Balance Summary'!$H$2,GL!$F$2:$F$19727,'Trial Balance Summary'!G2)

Now whatever date you put in H2 will change your results...