Question posted 2011 · +9 upvotes
I am trying to count the number of rows in a spreadsheet which contain at least one non blank value over a few columns: i.e.
row 1 has a text value in column A
row 2 has a text value in column B
row 3 has a text value in column C
row 4 has no values in A, B or C
the formula would equate to 3 because rows 1, 2 & 3 have a text value in at least one column. Similarly if row 1 had a text value in each column (A, B & C) this would be counted as 1.
Accepted answer +14 upvotes
With formulas, what you can do is:
- in a new column (say col D – cell
D2), add=COUNTA(A2:C2) - drag this formula till the end of your data (say cell
D4in our example) - add a last formula to sum it up (e.g in cell
D5):=SUM(D2:D4)
Top excel-vba Q&A (6)
- How to clear the entire array? +58 (2010)
- How to change Format of a Cell to Text using VBA +55 (2011)
- Download attachment from Outlook and Open in Excel +43 (2012)
- Can a VBA function in Excel return a range? +36 (2009)
- 2 Dimensional array from range +34 (2013)
- Hiding an Excel worksheet with VBA +33 (2009)
excel-vba solutions on this site
.