Google Sheets COUNTIF formula builder: count, add up or average the rows that match

Fill in the column, say what to look for, and copy the formula. It counts the rows that match, or adds up or averages a second column for those rows. You can add a second test (both must be true). Or count how many times each value appears, to find repeats. It runs in your browser and nothing you type here is sent anywhere. Every formula below was run in a real Google Sheet on 3 November 2026 (upper and lower case, blanks, repeated values, a decimal, a negative number, symbols like * and ?) and compared with an independent count, once in a US-locale spreadsheet and once in a German-locale one with semicolons: 64 checks, all matched.

How to use it

Paste the formula into one empty cell. For "count", "add up" and "average" the answer is a single number. For the last two choices, put the formula in the first row next to your data; it fills the whole column, so the cells below it must be empty. COUNTIF takes one test. When you add a second test the builder switches to COUNTIFS, SUMIFS or AVERAGEIFS, and a row has to pass both tests. Upper and lower case are the same in all of them: "Apple", "apple" and "APPLE" are one value.

What the tests become

The catches (found by testing in a real Sheet)

• "Is empty" counts the empty cells below your data too. With an open range like A2:A our table of 12 rows answered 988, where A2:A13 answered 1, because the open range runs to the bottom of the sheet. The same goes for "is not" and "does not contain": they also count the empty cells, so "<>apple" gave 995 where A2:A13 gave 8. Type an end row (A2:A13) for these tests.
• A cell that holds ="" is both empty and not empty. A formula that returns nothing is counted by the "is empty" test and also by "is not empty". We saw 2 empty and 12 not empty in a 13-row range that has one real blank and one ="".
• Stars and question marks. In a test, * and ? stand for "anything". Text you type here is escaped (~*) so "a*b" finds only "a*b". When the value comes from a cell, it is not escaped: a cell holding a* matched 8 of our 12 rows. The "how many times" choices are escaped for you; without it, "a*b" was counted 3 times instead of once.
• Numbers typed as text match too. "=10" counted 4 text "10" cells and 2 real numbers 10 as 6. Comparisons like "greater than" count only real numbers.
• Decimal comma. In a German-locale sheet the test "=7.5" found nothing; it must be written "=7,5". When you pick the semicolon separator, the builder writes the comma for you.
• Average of nothing. If no row passes, AVERAGEIF shows #DIV/0!; wrap it in IFERROR(..., 0) if you want a 0.
• Dates. Put the date in a cell and pick "a cell"; the test then compares real dates.

No code: KeepSorted

The add-on sorts the original table in place without code: no second copy, so formatting, checkboxes and notes move with their rows and people keep editing the same table. Select the range, pick the sort columns, Save. Free: one area per tab, up to 2 sort columns, no trial clock. Pro ($5/month or $39/year): up to 5 sort columns, custom orders like High > Medium > Low, ticked checkbox rows to the bottom, stacked tables sorted separately, and sorting after form responses, other editors' changes and an hourly check.

More KeepSorted tools and guides

• CONCATENATE formula builder (join cells, columns, ranges)
• SPLIT formula builder (text to columns or rows)
• VLOOKUP formula builder (XLOOKUP, INDEX MATCH, whole column)
• Running total and percent-of-total formula builder
• RANK formula builder (ties, rank within a group)
• FILTER formula builder (text, number, date conditions)
• Unique values formula maker (UNIQUE, counts, duplicates)
• Sort formula generator (SORT, SORTN, QUERY order by)
• Auto sort script generator (Apps Script that sorts the original)
• StaySorted alternative: an honest comparison

Sources

• Google Docs Editors function list (COUNTIF, COUNTIFS, SUMIF, SUMIFS, AVERAGEIF, AVERAGEIFS, SUBSTITUTE, MAP)