COUNTIFS Function in Excel
Quick answer
COUNTIFS counts the rows that meet every condition you give it, like sales in one menu category, or invoices from one supplier over $1,000 dated inside the financial year. Reach for it when COUNTIF isn’t enough because two or more conditions need to be true at the same time.
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)Works in: Excel 365, Excel 2024, 2021, 2019, 2016 and Excel for the web (and every version back to Excel 2007), on Windows and Mac. Google Sheets has COUNTIFS with the same syntax. In Google Sheets, the SUM pattern in the Guru Tip below needs SUMPRODUCT in place of SUM.
Syntax
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)| Argument | Required? | What it does |
|---|---|---|
criteria_range1 | Yes | The first range to test, such as the Category column. |
criteria1 | Yes | The condition for criteria_range1: a value ("Coffee"), a comparison in quotes (">1000"), a cell reference (J2) or a wildcard pattern ("*Dairy*"). |
criteria_range2, criteria2, ... | No | More range and condition pairs, up to 127 pairs in total. Every extra range must be the same size and shape as criteria_range1. Default: none, so COUNTIFS behaves like COUNTIF. |
How COUNTIFS works
Think of COUNTIFS as a checklist it runs down your data one row at a time. For each row, it checks every range and condition pair you’ve given it. If the row passes all of them, the count goes up by one. Miss even one condition and that row isn’t counted. That’s AND logic.
If you’re coming from SUMIFS, notice what’s missing: there’s no sum_range. COUNTIFS isn’t adding anything up, so every argument is one half of a range and condition pair, and the answer is simply how many rows passed. The ranges line up by position, so they all have to be the same size and start on the same row. You can also use the same range twice, which is how you count between two dates.
A condition can be exact text ("Coffee"), a number, or a comparison in quotes (">1000"). When a comparison uses a cell, the operator stays in quotes and joins the cell with an ampersand: ">="&J2. The wildcards * (any characters) and ? (one character) handle partial matches, "" matches blank cells, and text matches ignore case.
Examples
The examples use two sheets from the Bluegum Café workbook. The Basic example uses the Sales sheet, the same 12 rows of takings as the SUMIFS entry. The other two use the Invoices sheet, extended to 12 supplier bills from July 2025 to July 2026, with a Paid Date column added in H. A blank Paid Date means the bill is still outstanding. Both sheets hold their data in A1:H13 with headers in row 1.
Sales sheet:
| Date | Day | Category | Item | Qty | Unit Price | Total (inc GST) | GST |
|---|---|---|---|---|---|---|---|
| 01/07/2025 | Tue | Coffee | Flat white | 42 | $5.20 | $218.40 | $19.85 |
| 01/07/2025 | Tue | Food | Banana bread | 18 | $6.50 | $117.00 | $10.64 |
| 01/07/2025 | Tue | Tea | English breakfast | 15 | $4.50 | $67.50 | $6.14 |
| 19/07/2025 | Sat | Coffee | Latte | 55 | $5.50 | $302.50 | $27.50 |
| 19/07/2025 | Sat | Food | Toasted sandwich | 24 | $11.00 | $264.00 | $24.00 |
| 14/08/2025 | Thu | Coffee | Flat white | 38 | $5.20 | $197.60 | $17.96 |
| 14/08/2025 | Thu | Tea | Chai latte | 20 | $5.50 | $110.00 | $10.00 |
| 06/09/2025 | Sat | Food | Banana bread | 22 | $6.50 | $143.00 | $13.00 |
| 06/09/2025 | Sat | Coffee | Long black | 30 | $4.80 | $144.00 | $13.09 |
| 30/09/2025 | Tue | Tea | English breakfast | 12 | $4.50 | $54.00 | $4.91 |
| 01/10/2025 | Wed | Coffee | Flat white | 40 | $5.20 | $208.00 | $18.91 |
| 01/10/2025 | Wed | Food | Toasted sandwich | 16 | $11.00 | $176.00 | $16.00 |
Invoices sheet:
| Invoice No | Date | Supplier | Category | Amount ex GST | GST | Total | Paid Date |
|---|---|---|---|---|---|---|---|
| INV-1001 | 03/07/2025 | Ridgeline Roasters | Coffee beans | $480.00 | $48.00 | $528.00 | 10/07/2025 |
| INV-1002 | 04/07/2025 | Coastal Dairy Co | Milk | $215.50 | $21.55 | $237.05 | 11/07/2025 |
| INV-1003 | 15/08/2025 | Ridgeline Roasters | Coffee beans | $960.00 | $96.00 | $1,056.00 | 29/08/2025 |
| INV-1004 | 02/09/2025 | Eco Pack Supplies | Packaging | $312.00 | $31.20 | $343.20 | 16/09/2025 |
| INV-1005 | 10/10/2025 | Barwon Espresso Services | Equipment | $1,250.00 | $125.00 | $1,375.00 | 24/10/2025 |
| INV-1006 | 21/11/2025 | Ridgeline Roasters | Coffee beans | $1,120.00 | $112.00 | $1,232.00 | 05/12/2025 |
| INV-1007 | 16/01/2026 | Coastal Dairy Co | Milk | $198.40 | $19.84 | $218.24 | 30/01/2026 |
| INV-1008 | 20/02/2026 | Ridgeline Roasters | Coffee beans | $890.00 | $89.00 | $979.00 | 06/03/2026 |
| INV-1009 | 17/04/2026 | Ridgeline Roasters | Coffee beans | $1,045.00 | $104.50 | $1,149.50 | |
| INV-1010 | 29/05/2026 | Coastal Dairy Co | Milk | $230.00 | $23.00 | $253.00 | |
| INV-1011 | 19/06/2026 | Surf Coast Cleaning | Cleaning | $145.00 | $14.50 | $159.50 | |
| INV-1012 | 10/07/2026 | Ridgeline Roasters | Coffee beans | $1,180.00 | $118.00 | $1,298.00 |
Basic: Count the sales in one menu category
On the Sales sheet, type a category in J2 and put the formula in K2.
=COUNTIFS(C2:C13, J2)With Coffee in J2, the result is 5, because five rows on the sheet are coffee sales. Change J2 to Food and it’s 4, and Tea gives 3. It’s counting rows, not cups: for the number of coffees sold, you’d use SUMIFS on the Qty column. With a single pair like this, COUNTIFS does the same job as COUNTIF.
Real-world: Count a supplier’s invoices over $1,000 this financial year
On the Invoices sheet, put the financial year start in J2 (01/07/2025), the end in J3 (30/06/2026), and the formula in J4.
=COUNTIFS(C2:C13, "Ridgeline Roasters", G2:G13, ">1000", B2:B13, ">="&J2, B2:B13, "<="&J3)The result is 3: INV-1003, INV-1006 and INV-1009, the Ridgeline Roasters invoices over $1,000 (inc GST) dated inside FY26. Every condition knocks something out. INV-1005 is $1,375.00 but from a different supplier, INV-1001 and INV-1008 are under $1,000, and INV-1012 is $1,298.00 but dated 10/07/2026, which is the next financial year. The ">1000" is typed straight into the formula, while the dates sit in cells and are joined on with &, so next July you change J2 and J3 and leave the formula alone.
Advanced: Count outstanding invoices with a wildcard and a blank test
On the Invoices sheet, type part of a supplier name in J6 and put the formula in J7.
=COUNTIFS(C2:C13, "*"&J6&"*", H2:H13, "")With Ridgeline in J6, the result is 2: INV-1009 and INV-1012 are the Ridgeline Roasters invoices with no Paid Date. The asterisks either side of J6 mean “contains”, so you only need to type part of the name, and "" matches the blank cells in the Paid Date column. Type Dairy and you get 1. Clear J6 altogether and the pattern matches every supplier, so you get 4, the total number of outstanding invoices.
Guru Tip
COUNTIFS only does AND. When I need OR, like Coffee or Tea, I either add two COUNTIFS together or wrap one in SUM with the options in curly braces: =SUM(COUNTIFS(C2:C13, {"Coffee","Tea"})), which gives 8 on the Sales sheet. That’s safe here because a row can’t be both Coffee and Tea. The trap is when one row can meet both conditions. Count Coffee sales plus Saturday sales and you get 5 + 4 = 9, but the two Saturday coffee rows are in there twice. I take the overlap back off with a third COUNTIFS that tests both conditions, which gives the right answer of 7.
Common errors
#VALUE!: criteria ranges are different sizes
Cause: One of your criteria ranges doesn’t cover the same number of rows and columns as the others, such as C2:C13 paired with H2:H12. Fix: Make every range start and end on the same rows. Formatting the data as a Table (Ctrl+T) and using column references keeps them in step as rows are added.
#VALUE!: the source workbook is closed
Cause: The formula points at ranges in another workbook that isn’t open. COUNTIFS can’t read closed files. Fix: Open the source workbook and the result comes back. If it has to stay closed, use SUMPRODUCT instead, e.g. =SUMPRODUCT((C2:C13="Ridgeline Roasters")*(H2:H13="")).
0 instead of a count: nothing matched
Cause: This isn’t an error as far as Excel is concerned, which is why it’s easy to miss. No row met every condition. The usual suspects are numbers stored as text (a comparison like ">1000" skips them), stray spaces ("Coffee " is not "Coffee"), and dates that are really text, either in the data or typed into the criteria cell in a format Excel can’t read as a date. Fix: Test one condition at a time to find the one returning 0. Check suspect cells with =ISNUMBER(B2), which should be TRUE for real numbers and dates. Clean spaces with TRIM, convert text numbers and dates with Data > Text to Columns, and retype the criteria date so it right-aligns in the cell.
Grab the template: COUNTIFS
Get the Bluegum Café Sales and Invoices sheets with all three COUNTIFS examples built in, so you can swap in your own categories, suppliers and financial year dates.