Challenge Scenario
In accounts receivable (AR) management, treasury teams must maintain an accurate ledger of settled versus outstanding invoices to forecast weekly working capital and plan supplier disbursements.
As a Credit & Collections Specialist, you are reconciling the current receivables ledger across 5 corporate clients:
- Paid Total (B9): Sum all dollar amounts where Status equals
"PAID".
- Pending Total (B10): Sum all uncollected dollar amounts where Status equals
"PENDING".
- Invoice Counts (C9 & C10): Tally the discrete count of invoices in each status category using
COUNTIF.
Your Sheet Layout
Before writing your conditional summary formulas, examine how this accounts receivable sheet is organized. Separating individual client invoice details from executive cashflow summaries ensures transparent forecasting and clean checks.
The sheet is organized into two distinct functional zones:
- Receivables Invoicing Zone (Columns A–D): Contains customer invoice numbers, client company names, invoiced amounts ($), and current payment settlement status (PAID or PENDING).
- Cashflow & Status Summary (Rows 8–10): Calculates conditional totals for collected cash ("PAID") and uncollected receivables ("PENDING"), while counting the number of invoices in each status.
| Cell Range |
Column Name |
Section |
Formula / Approach |
What It Does |
A2:D6 |
Invoice #, Client, Amount, Status |
Data (Receivables Ledger) |
Customer invoice records |
Read-only billing entries with amounts in Column C and status in Column D. |
B9 |
Total Paid Amount ($) |
Summary (Cash Collected) |
=SUMIF(D2:D6, "PAID", C2:C6) |
Sum total dollar value of all settled invoices marked as PAID. |
B10 |
Total Pending Amount ($) |
Summary (Receivables Pipeline) |
=SUMIF(D2:D6, "PENDING", C2:C6) |
Sum total dollar value of all outstanding invoices marked as PENDING. |
C9 |
Paid Invoice Count |
Summary (Settled Volume) |
=COUNTIF(D2:D6, "PAID") |
Count the number of invoices with PAID status. |
C10 |
Pending Invoice Count |
Summary (Outstanding Volume) |
=COUNTIF(D2:D6, "PENDING") |
Count the number of invoices with PENDING status. |
This layout gives treasury managers a dynamic status matrix that updates automatically as pending invoices get paid.
Complete the objectives below in the live spreadsheet editor. Each objective card will turn green once your formula evaluates successfully.
1
In cell B9, calculate total amount for PAID invoices using SUMIF.
2
In cell B10, calculate total amount for PENDING invoices using SUMIF.
3
In cell C9, count number of PAID invoices using COUNTIF.
4
In cell C10, count number of PENDING invoices using COUNTIF.
Solution & Formula Breakdown
Conditionally summing and counting amounts by category is a foundational spreadsheet task. Let's explore how SUMIF and COUNTIF work together.
1. Conditional Dollar Totals with SUMIF (B9 & B10)
=SUMIF(D2:D6, "PAID", C2:C6)
=SUMIF(D2:D6, "PENDING", C2:C6)
The SUMIF(range, criteria, [sum_range]) function works by matching criteria before summing:
- Paid Amount (B9): Checks Column D for
"PAID" and sums the matching values in Column C (2,400 + 3,800 + 1,200 = $7,400).
- Pending Amount (B10): Checks Column D for
"PENDING" and sums the matching values in Column C (1,500 + 2,900 = $4,400).
2. Conditional Counts with COUNTIF (C9 & C10)
=COUNTIF(D2:D6, "PAID")
=COUNTIF(D2:D6, "PENDING")
- Paid Count (C9): Counts how many rows in
D2:D6 contain "PAID" → 3 invoices.
- Pending Count (C10): Counts how many rows in
D2:D6 contain "PENDING" → 2 invoices.
Notice the order of arguments in SUMIF: first the criteria column (D2:D6), then what you're looking for ("PAID"), and last the numbers to sum (C2:C6). If you ever use SUMIFS (with an 'S' for multiple criteria), the numbers to sum come first instead!