Ledger Summery Report
Introduction-
Sometime we need for small project or working in the office Need to Manage data and analysis individual summery with more Details. Sorting in Individual information very long process and its time consuming. In this tutorial we are learning how to make individual summery report using only two formulas.
In the following Image Ledger report shown. In the ledger report find information according Code No. Code no is unique identification no allotted to each person. This information getting from Customer Billing Data. Each customer repeated more time.Lets start how to use Vlookup & Sumif. For Learn sumif function ..click here
Get Data using Vlookup
We are Getting information applying vlookup. Code is unique Identification number. In the cell C4 apply vlookup formula.This formula get given column information from the main data.
Here Require Lookup Value, Table Array, Col index number & range lookup.
Lookup Value
Lookup value is searching Code C3, because we are finding information according Code. If we are finding information according name then lookup value will be Name. You can use name or code. Here we are use code. Code is simple for putting searching cell.
Table Array
Table Array is main data. Here our main data is B11:H23. Main data always select from Lookup value column. If criteria is select name then Table array will be C11:H23.
Co. Index Number (Column index Number)
Column index number is for information which column number from the lookup value.
Here we are finding name. Name is available from the code, 2nd number column. address 3rd so on. Range Lookup
Lookup range is always 0 or False.
In the cell C4 Press =VLOOKUP(
Then click cell c3 press comma
then select table B11:H23. press comma
Put column index number 2 press comma then 0
Enter .
You will get Name from Main Data.
For getting Address apply same formula. Change only column index number 3 instead of 2.
Get Contact number & Address using same formula.
Get Total summery value using SUMIF Formula
For balance Less Received amount from bill amount.
Sum if function make sum according selected Criteria only.
We are Applying formula by code. Sumif function calculate total that contains code.
Range –
Here range is that entered code numbers. B12:B23Criteria –Criteria is that entered searching code.
Cell C3Sum Range –
Here we are finding bill amount sum. sum range is G12:G23.
In the cell C8 type =SUMIF(
Select range from column B. that is B12:B23. Press Comma.
Click on criteria cell C3. Press comma.
Thaen Select sum range from column G. means G12:G23. Press Enter.
=SUMIF(B13:B24,C3,G13:G24)
Sumif calculate sum in the respected criteria code.
For calculate total amount recd apply sumif formula selecting Amount recd sum range H12:H23.
=SUMIF(B13:B24,C3,H13:H24)
=C8-D8
Enter Different code in the Yellow highlighted cell C3. You will get result.
Get Total summery value using SUMIF Formula
For balance Less Received amount from bill amount.
Sum if function make sum according selected Criteria only.
We are Applying formula by code. Sumif function calculate total that contains code.
Range –
Here range is that entered code numbers. B12:B23Criteria –Criteria is that entered searching code.
Cell C3Sum Range –
Cell C3Sum Range –
Here we are finding bill amount sum. sum range is G12:G23.
In the cell C8 type =SUMIF(
Select range from column B. that is B12:B23. Press Comma.
Click on criteria cell C3. Press comma.
Thaen Select sum range from column G. means G12:G23. Press Enter.
=SUMIF(B13:B24,C3,G13:G24)
Sumif calculate sum in the respected criteria code.
For calculate total amount recd apply sumif formula selecting Amount recd sum range H12:H23.
=SUMIF(B13:B24,C3,H13:H24)
=C8-D8
Enter Different code in the Yellow highlighted cell C3. You will get result.
