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.
Add a comment
|
3 Answers
Please use
=QUERY(INDEX(A1:A&B1:B),"select count(Col1)",0)
answered Jan 15, 2021 at 12:45
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.
answered Jan 15, 2021 at 8:31
-
-
1Please see update to formula, swapping the first A:A for ROW(A:A). I updated your sheet. 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
-
1See 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. Jan 16, 2021 at 3:49
-
1The 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. Jan 16, 2021 at 7:13
I used this and solved as well
=countifs(A1:A,"<>")+countifs(A1:A,"",B1:B,"<>")