1

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

2

Please use

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

enter image description here

0
1

Try this:

=COUNTA(IFERROR(FILTER(ROW(A:A),A:A&B:B<>"")))

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.

6
  • It didn't work //docs.google.com/spreadsheets/d/… Commented 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
    Commented 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<>"") Commented 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
    Commented 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
    Commented Jan 16, 2021 at 7:13
1

I used this and solved as well

=countifs(A1:A,"<>")+countifs(A1:A,"",B1:B,"<>")
1
  • Good work Hassan :) Commented Jan 19, 2021 at 22:52

Your Answer

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

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