How to use the SUMIFS function to create a summary table of bank accounts in Excel
How to use the SUMIFS function to create a summary table of bank accounts in Excel: In this article, Buffcom.net will share with you how to use the SUMIFS function to create a summary table of bank accounts in Excel.
1. SUMIFS function syntax
Syntax: =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
Where:
- Sum_range: Required argument, the range of cells to sum.
- Criteria_range1: Required argument, the range of cells containing the first criterion.
- Criteria: Required argument, the first criterion.
- Criteria_range2…: Optional argument, additional ranges of cells containing additional criteria.
- Criteria…: Optional argument, additional criteria.
Note: If the criterion is text or contains mathematical symbols, it must be enclosed in double quotation marks (“ “).
2. How to use the SUMIFS function to create a summary table of bank accounts
For example, suppose we have a summary table of bank accounts and a general journal as shown below. We need to summarize the debit and credit transactions for each account to calculate the ending balance for each account.
How to use the SUMIFS function to create a summary table of bank accounts in Excel
Step 1: Because the data source is large, the first thing we need to do is name the data range for easier formula writing.
In this article, I named 4 ranges as Date, Debit, Credit, and Amount corresponding to the Date, Debit, Credit, and Amount columns in the GJ sheet.
How to use the SUMIFS function to create a summary table of bank accounts in Excel
Step 2: Apply the SUMIFS function structure above to calculate the total debit transactions of account 1121 from January 1, 2020, to June 30, 2020, as follows:
=SUMIFS(Amount, Debit, A7, Date, ”>=”&$G$3, Date, ”<=”&$G$4)
Copy the formula for the other accounts to get the result.
How to use the SUMIFS function to create a summary table of bank accounts in Excel
Step 3: We have the formula to calculate the total credit transactions of account 1121 from January 1, 2020, to June 30, 2020, as follows:
=SUMIFS(Amount, Credit, A7, Date, ”>=”&$G$3, Date, ”<=”&$G$4)
Copy the formula for the other accounts to get the result.
Step 4: After calculating the total debit and credit transactions, we can calculate the ending balance with the formula:
=D7+E7-F7
Copy the formula for the other accounts to get the complete summary table of bank accounts.
How to use the SUMIFS function to create a summary table of bank accounts in Excel
Thus, the article has guided you on how to use the SUMIFS function to create a summary table of bank accounts. Hopefully, this article will be useful to you in your work. Wish you success!
