How to Use Excel SUMIF Formulas for Paid, Outstanding, and Available Loan Amounts
Question details
The user needs to calculate specific loan totals including paid amounts, outstanding loans, and the remaining available umbrella limit based on dynamic statuses.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Managing a loan worksheet with disbursement amounts and tracking specific statuses such as Ongoing, Paid, and Late.
- Observed behavior
- Requires accurate conditional sums grouped by loan status, and a formula to deduct ongoing and late loan amounts from a fixed overall limit.
Ensure your loan data is organized with clear column headers, placing loan amounts in one specific column and their corresponding statuses (Paid, Ongoing, Late) in another adjacent column.
Calculate Conditional Loan Totals Using the SUMIF Function
Use the SUMIF function to evaluate your loan statuses and sum the corresponding amounts automatically.
The SUMIF function allows you to sum values in a range that meet a single criteria. By setting the loan status as the criteria, you can easily separate out Paid, Ongoing, and Late amounts.
In the steps below, we assume your loan amounts are listed in cells A2:A6 and their statuses are in cells B2:B6.
Select the cell where you want the 'Paid' total to appear. Type the formula =SUMIF(B2:B6,"Paid",A2:A6) and press Enter. This adds up all amounts in column A where column B contains the word 'Paid'.
Select a new cell for your 'Outstanding' total. Enter the formula =SUMIF(B2:B6,"Ongoing",A2:A6) and press Enter. This isolates and sums only the ongoing loan amounts.
To find your available remaining limit, subtract both the Ongoing and Late loans from your total limit. Assuming your total limit is 25,000,000,000, type the formula =25000000000-SUM(SUMIF(B2:B6,"Ongoing",A2:A6),SUMIF(B2:B6,"Late",A2:A6)) and press Enter.
Manage Complex Financial Data Easily with WPS Spreadsheet
WPS Spreadsheet fully supports SUMIF, SUMIFS, and other advanced financial functions. It provides an intuitive interface for tracking loan disbursements, analyzing statuses, and building dynamic financial models without any lag.
- 1. Open Your Data: Launch WPS Spreadsheet and open your loan tracking worksheet.
- 2. Insert the Function: Click on the target cell, navigate to the 'Formulas' tab on the top ribbon, and click 'Insert Function'.
- 3. Search for SUMIF: Type 'SUMIF' in the search bar, select it, and fill out the Range, Criteria, and Sum_range fields in the prompt dialog.
- 4. Apply and Drag: Click OK to calculate. You can lock your ranges with absolute references (like $A$2:$A$6) and drag the formula down to apply it to other statuses.
Frequently Asked Questions
Can I use multiple conditions for my loan calculations?
Yes. If you need to filter by multiple criteria at once (for example, loan status AND a specific disbursement date), use the SUMIFS function instead of SUMIF. SUMIFS allows you to define multiple range and criteria pairs.
Why is my SUMIF formula returning a 0 instead of the correct total?
This commonly happens if there are trailing spaces in your cell data (e.g., typing 'Paid ' instead of 'Paid') or if your criteria range and sum range are misaligned. Ensure ranges like A2:A6 and B2:B6 exactly match in size.
How do I make my formula ranges absolute so they don't shift when copied?
Highlight the range reference in your formula bar and press the F4 key on your keyboard. This will add dollar signs to the reference (e.g., changing A2:A6 to $A$2:$A$6), locking the range in place.
