When you need an Excel COUNTIF invoices by payment status check, use COUNTIF to count one exact status in the exported status column. It gives a simple reconciliation control: compare the returned count with the number of Open items you expect to investigate or carry forward.
Quick answer: count Open invoice statuses
To count Open invoices in a payment-status export, enter =COUNTIF(A1:A3,"Open") in C1. With Open, Paid, and Open in A1:A3, C1 returns 2.
Synthetic payment-status input: A1 contains Open, A2 contains Paid, and A3 contains Open.
| Cell | Payment status |
|---|---|
| A1 | Open |
| A2 | Paid |
| A3 | Open |
Enter =COUNTIF(A1:A3,"Open") in C1. The range A1:A3 is the payment-status column, and "Open" is the one status to count.
C1 returns 2, meaning two invoice rows are assigned the Open status.
Excel COUNTIF invoices by payment status: worked reconciliation check
This example uses a deliberately small synthetic export so that every counted row is visible. In a real file, select only the cells that contain payment statuses; do not include unrelated notes, totals, or a header in the range.
- Place the statuses in one range. Put the three status values shown above in A1:A3. Treat each row as one invoice record. The invoice number can be elsewhere in the export; this formula needs only the payment-status cells.
- Put the result in a separate cell. Select C1, enter
=COUNTIF(A1:A3,"Open"), and press Enter. Keeping the result outside the source range makes the check easier to review and prevents a result from being mistaken for another status. - Read the count in the reconciliation context. The formula returns 2 because A1 and A3 equal Open. Compare 2 with the Open-invoice count from the report or worklist you are reconciling. If the numbers differ, filter the export or inspect the status values before treating the count as final.
COUNTIF(range, criteria) has two inputs. A1:A3 tells Excel where to look, while "Open" is the text criterion. Microsoft describes COUNTIF as the function for counting cells that meet one criterion in a range in its COUNTIF function guidance.
Check the zero-result boundary
Use the same formula when no invoice has the status you are checking for. Replace the synthetic values in A1:A3 with Paid, Closed, and Paid, then leave C1 as =COUNTIF(A1:A3,"Open").
| Cell | Payment status |
|---|---|
| A1 | Paid |
| A2 | Closed |
| A3 | Paid |
With Paid, Closed, and Paid in A1:A3, =COUNTIF(A1:A3,"Open") returns 0; this is a valid no-match result, not a formula error.
For a reconciliation, zero means the selected range contains no exact Open status. It does not prove the entire workbook has no open invoices. Confirm that the formula range covers the full exported status column and that the export has the expected rows.
Common status-count problems
| Observable symptom | Likely cause | Concrete fix |
|---|---|---|
| The count is lower than expected. | One or more imported statuses have a leading or trailing space. | Clean the source status text, then recount. See how to fix COUNTIF trailing spaces before using the result in a reconciliation. |
| The count includes rows written as OPEN. | COUNTIF ignores upper and lower case in text criteria. | Use a case-sensitive check only if capitalization is meaningful in the export; otherwise standardize status codes. |
| The count is zero when Open rows appear elsewhere on the sheet. | The range points to only part of the payment-status column. | Extend the range to include every invoice-status row, while excluding the header and summary rows. |
COUNTIF text matching is not case-sensitive, so Open and OPEN are both counted by the criterion "Open". Microsoft also notes that unintended spaces and nonprinting characters can make text counts behave unexpectedly. For short operational labels, a consistent status-code check can help identify values that should not be mixed in one count.
When COUNTIF is not enough
COUNTIF evaluates one criterion only: it counts Open statuses but does not also restrict the count by supplier, month, amount, or another field. If you later need a count such as Open invoices for one supplier, use COUNTIFS with a status range/criterion pair and a supplier range/criterion pair. Microsoft distinguishes COUNTIF for one value from COUNTIFS for counts that must meet criteria across multiple ranges in its guide on counting how often a value occurs.
This method also does not count rows by cell color, distinguish status capitalization, or validate whether an invoice was legitimately paid. It counts matching text in the cells you specify.
Compatibility and next step
COUNTIF is available in Excel for Microsoft 365, Excel 2024, Excel 2021, Excel 2019, and Excel 2016, including the applicable Mac versions listed by Microsoft. The formula syntax and single-criterion behavior used here do not require a newer dynamic-array feature.
Once the Open count is reliable, use the same approach for another exact workflow label such as Pending or On Hold. If an export contains several inconsistent labels, first review the status codes, then count each approved code separately rather than combining unlike text values in one criterion.
Worked example details and boundary cases
A status with an unintended leading or trailing space can produce an unexpectedly low count; remove those spaces before relying on the reconciliation result.
Practice with the exact example data
Use a separate blank worksheet for each example. Enter each value at its listed cell address, then type the formula in AA1. Quoted text is shown in JSON notation to preserve spaces: enter the characters inside the quotes, without the quotes, using a leading apostrophe to keep text and spaces literal. An empty quoted string means an explicit empty text value; use an apostrophe entry where appropriate. Compare your result with the expected result below. These are synthetic practice data.
Example 1 — Main case
| Cell | Input value |
|---|---|
| A1 | “Open” |
| A2 | “Paid” |
| A3 | “Open” |
Formula in AA1: =COUNTIF(A1:A3,"Open")
Expected result: 2
Example 2 — Boundary case
| Cell | Input value |
|---|---|
| A1 | “Paid” |
| A2 | “Closed” |
| A3 | “Paid” |
Formula in AA1: =COUNTIF(A1:A3,"Open")
Expected result: 0
These examples were checked with a limited calculator supporting numeric aggregates, ASCII text functions, exact criteria and exact first-match XLOOKUP. Only the displayed examples and supported semantics were calculated. They were not executed in Microsoft Excel. This check does not verify every claim in the article.
Prepared with automated drafting and review, official references, and a limited example calculator. Synthetic examples are provided for practice; no independent human review or Microsoft Excel execution is claimed.