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.


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/… – Hassan Almaateeq Jan 15 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 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<>"") – Hassan Almaateeq Jan 15 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 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 at 7:13

I used this and solved as well


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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