I would like to use counta function to count rows that are not empty based on column A or B.

In other words.. if either column A or B not empty, it should be counted.

3 Answers 3


Please use

=QUERY(INDEX(A1:A&B1:B),"select count(Col1)",0)

enter image description here


Try this:


ROW(A:A) gives us "something" to count.

By concatenating each row-cell of A:A with the corresponding row-cell of B:B, we form one string, which we can evaluate as one string and thus count as one string.

  • It didn't work //docs.google.com/spreadsheets/d/… Jan 15, 2021 at 10:24
  • 1
    Please see update to formula, swapping the first A:A for ROW(A:A). I updated your sheet.
    – Erik Tyler
    Jan 15, 2021 at 13:15
  • That's good but it will always return a value of 1 if all cells are empty!. What about using countif (two criteria like A:A<>""&B:B, A:A&B:B<>"") Jan 15, 2021 at 15:38
  • 1
    See updated formula with IFERROR wrap included. If this does the trick, please be sure to mark the post as "Best Answer" to signal to others that the issue has been fully addressed.
    – Erik Tyler
    Jan 16, 2021 at 3:49
  • 1
    The reason that you were getting a 1 with all blanks is that the FILTER produced no results, which throws an #NA error. Since an error of any kind is "something," COUNTA was counting the error as 1. IFERROR( ) with no second argument simply instructs Sheets to return null (i.e., nothing) instead of an error. As for ROW(A:A), again, it just gives something constant to FILTER based upon, since very row, filled or blank, will have a row number. Again, if this satisfies your posted request, please mark my post as "Best Answer" to signify that it has been resolved. Thank you.
    – Erik Tyler
    Jan 16, 2021 at 7:13

I used this and solved as well

  • Good work Hassan :) Jan 19, 2021 at 22:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.