Conditional Aggregation
Sum If Blank ("") vs. Not Blank ("<>") With SUMIF & SUMIFS
Total open accounts-receivable invoices where Paid_Date is blank using SUMIF(D2:D6, "", C2:C6), settled invoices where Paid_Date is non-blank using "<>", and multi-criteria open region balances with SUMIFS.
Audit unpaid open invoices vs. collected cash directly from blank Paid_Date cells!
In accounts-receivable ledgers, purchase-order trackers, and shipment logs, an empty cell in column D (Paid_Date or Shipped_Date) carries vital operational meaning: the invoice is still unpaid or the order has not yet shipped!
Spreadsheet aggregation functions recognize two dedicated blank-matching criteria strings: "" matches empty cells (including formula-returned "" strings) to sum unpaid balances via SUMIF(D2:D6, "", C2:C6), while "<>" matches every non-blank cell to sum collected cash via SUMIF(D2:D6, "<>", C2:C6)! Combine "" inside SUMIFS(C2:C6, B2:B6, "EMEA", D2:D6, "") to isolate open unpaid balances for a single sales region!
| Ledger Aggregation Goal | Criteria Operator String | Formula Pattern | Ledger Result |
|---|---|---|---|
| All Unpaid Open Invoices (Blank Paid_Date) | "" (Empty String) | =SUMIF(D2:D6, "", C2:C6) | $4,150 Open AR |
| All Settled Paid Invoices (Non-Blank) | "<>" (Not Empty) | =SUMIF(D2:D6, "<>", C2:C6) | $3,100 Collected |
| Open EMEA Invoices Only | "EMEA" + "" | =SUMIFS(C2:C6, B2:B6, "EMEA", D2:D6, "") | $2,900 Open EMEA |
Sum Values Where Criteria Column Is Blank: SUMIF(D2:D6, "", C2:C6)
Totals Amount_USD (C2:C6) for every row whose Paid_Date cell (D2:D6) is empty.
=SUMIF(D2:D6, "", C2:C6)Sum Values Where Criteria Column Is Not Blank ("<>") or Filtered by Region
Totals settled invoices with "<>" or filters open balances by Region (B2:B6 = "EMEA") via SUMIFS.
=SUMIFS(C2:C6, B2:B6, "EMEA", D2:D6, "")Try the example
Use the original values or change them to test your understanding.