How to use the SUMIFS function to create a summary table of bank accounts in Excel
![](https://buffcom.net/wp-content/uploads/2023/04/How-to-use-the-SUMIFS-function-to-create-a-summary-table-of-bank-accounts-in-Excel.jpg)
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 1 How to use the SUMIFS function to create a summary table of bank accounts in Excel](https://buffcom.net/wp-content/uploads/2023/04/How-to-use-the-SUMIFS-function-to-create-a-summary-table-of-bank-accounts-in-Excel2.jpg)
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 2 How to use the SUMIFS function to create a summary table of bank accounts in Excel](https://buffcom.net/wp-content/uploads/2023/04/How-to-use-the-SUMIFS-function-to-create-a-summary-table-of-bank-accounts-in-Excel3.jpg)
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 3 How to use the SUMIFS function to create a summary table of bank accounts in Excel](https://buffcom.net/wp-content/uploads/2023/04/How-to-use-the-SUMIFS-function-to-create-a-summary-table-of-bank-accounts-in-Excel4.jpg)
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 5 How to use the SUMIFS function to create a summary table of bank accounts in Excel](https://buffcom.net/wp-content/uploads/2023/04/How-to-use-the-SUMIFS-function-to-create-a-summary-table-of-bank-accounts-in-Excel6.jpg)
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!