Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to count rows where any column has a value in Excel and Google Sheets
Heads up: Excel and Google Sheets do this differently.
Excel
=SUMPRODUCT((MMULT((A2:C5<>"")*1,{1;1;1})>0)*1)Google Sheets
=SUMPRODUCT((MMULT((A2:C5<>"")*1,TRANSPOSE(ROW(INDIRECT("A1:A"&COLUMNS(A2:C5)))))>0)*1)Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Identify the full range of columns you want to check — for example A2:C100.
- 2In Excel use =SUMPRODUCT((MMULT((A2:C100<>"")*1,ROW(INDIRECT("A1:A"&COLUMNS(A2:C100)))^0)>0)*1).
- 3In Google Sheets the same MMULT approach works; alternatively use =SUMPRODUCT((MMULT(IF(A2:C100<>"",1,0),TRANSPOSE(IF(COLUMN(A2:C100),1,0)))>0)*1).
- 4A simpler per-column alternative: =SUMPRODUCT(((A2:A100<>"")+(B2:B100<>"")+(C2:C100<>""))>0)*1) — list each column explicitly.
Tips
- For a single-column check, just use =COUNTA(A2:A100). The multi-column approach is only necessary when any column could hold the value.
Need the Google Sheets version instead? Open Sheets variant in workspace →
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
What if I want to count rows where ALL columns have a value?
Change >0 to =COLUMNS(range) — the MMULT sum must equal the number of columns for the row to be fully populated.
Does this count formula-empty cells as blank?
Cells containing =IF(FALSE,"x","") return an empty string, which this formula treats as filled because <> "" is FALSE but *1 gives 0. Use ISBLANK() for stricter detection.