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 it for your exact data?
Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.
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.