google sheet: combine countifs() and trim()

user_1363960 0
Viewed 306

I use

=trim(cell)<>""

to check if the cell is not blank/null or white space.

I want to count how many row from row1 to row100 where there is no blank or null or any white space(s) in the column A or B. So it will count only if both column A and B has value (not blank/null/space).

When using countifs() to count item using multiple criteria. I can't use trim() as the parameter. For example :

countifs( trim(A2:A100) , "<>**" , trim(B2:B100) , "<>**")

It will give me error.

if i use :

countifs( A2:A100 , "<>**" , B2:B100 , "<>**")

That can't catch single or multiple space like " " or " " etc.

google-sheets
4 Answers

You can not use countifs for this problem.

As you can see in this article if you need complex condition you need to use "SUMPRODUCT" on this article referenced from the previews one.

For you problem this is the solution:

=SUMPRODUCT(--(TRIM(A1:A100)<>""), --(TRIM(B1:B100)<>""))

Explanation inside the article.

If you need more help with understand it let me know.

user_22187130

Try making the range an array forumla

countifs( {trim(A2:A100)} , "<>**" , {trim(B2:B100)} , "<>**")
user_89418940

Another approach:

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

user_139032830

try:

=INDEX(COUNTIFS(TRIM(A2:A100); "<>**"; TRIM(B2:B100); "<>**"))
user_56326290
Related