Question posted 2012 · +5 upvotes
I am trying to find a function that will return the number of cells within a given range that have a number in them. I want to count the number of responses that people gave, irregardless of the value they entered in the cell.
How do I do this?
Accepted answer +14 upvotes
If you are looking for a number of cells containing numeric values, then COUNT() function is what you’re looking for:
=COUNT(A1:D6)
If you are looking number of cells with non-blank values (numeric or otherwise), then COUNTA() is the right function:
=COUNTA(A1:D6)
The last formula works only if none of the cells have empty strings as values (i.e. none of the cells in the range have ="" or something equivalent in them). If that is the case, then this formula should be used instead:
=SUMPRODUCT((E7:G10<>"") * 1)
3 code variants in this answer
- Variant 1 — 1 lines, starts with
=COUNT(A1:D6) - Variant 2 — 1 lines, starts with
=COUNTA(A1:D6) - Variant 3 — 1 lines, starts with
=SUMPRODUCT((E7:G10<>"") * 1)
Top excel Q&A (6)
- Shortcut to Apply a Formula to an Entire Column in Excel +335 (2011)
- How should I escape commas and speech marks in CSV files so they work in Excel? +136 (2012)
- Convert xlsx to csv in linux command line +96 (2012)
- How to create a link inside a cell using EPPlus +50 (2011)
- IF statement: how to leave cell blank if condition is false ("" does not work) +44 (2013)
- T-SQL: Export to new Excel file +44 (2012)
excel solutions on this site
.