0

I'm looking get the count of each row where the cells are not empty in a none sequential range (A3:A;C3:C;E3:E;G3:G) . Say if A3 is not empty and C3, E3, G3 is empty than the result would be 1 Say if A3 and C3 is not empty and E3, G3 is empty than the result would be 2 Say if A3 , C3 and E3 is not empty and G3 is empty than the result would be 3

I like that done for the entire range.

BASICLY COUNT EACH CELL WITH ANY VALUE ON EACH ROW AND RETURN THE RESULTS OF THAT ROW.

I need the same results as =countA(A3:G) for each row inoring columns(B,D,F) in some type of an arrayformula.

Thanks for the help in advance

Wilson

link to the sheet: LINK TO SHEET

enter image description here

user2916405
  • 147
  • 1
  • 14

2 Answers2

1

Try below formula :

=Arrayformula(IF(A3:A<>"",1,0)+IF(C3:C<>"",1,0)+IF(E3:E<>"",1,0)+IF(G3:G<>"",1,0))
Rocky
  • 950
  • 1
  • 7
  • 12
  • That's it, almost perfect, thank you! Just one adjustment if possible. How do I remove the 0's if the row is empty? – user2916405 Jun 15 '20 at 11:23
  • @user2916405 Just replace all the `0`'s with `""`. `=Arrayformula(IF(A3:A<>"",1,"")+IF(C3:C<>"",1,"")+IF(E3:E<>"",1,"")+IF(G3:G<>"",1,""))` – marikamitsos Jun 15 '20 at 12:22
1

See if this helps

=ArrayFormula(if(len(A3:A); MMULT(N({A3:A\C3:C\ E3:E\ G3:G}<>""); {1; 1; 1; 1});))

Or, in a more general approach (filtering out the odd columns)

=ARRAYFORMULA(IF(LEN(A3:A); MMULT(N(A3:G<>""); N(TRANSPOSE(ISODD(COLUMN(A3:G3)))));))
JPV
  • 26,499
  • 4
  • 33
  • 48
  • You could probably update your answers [COUNTA in current row in an array formula (Google Sheets)](https://stackoverflow.com/a/30395824) on how to dismiss the `0`'s – marikamitsos Jun 15 '20 at 12:20
  • Thank you for the help. The first formula is perfect! – user2916405 Jun 15 '20 at 12:24
  • Hey @JPV :) Saw this on the google forums. you can 0 out the "don't count this column" columns, but using the second matrix in the mmult without worrying about the first. like this: =ARRAYFORMULA(ARRAY_CONSTRAIN(MMULT(N(A3:G<>"");{1;0;1;0;1;0;1});COUNTA(A3:A);1)) – MattKing Jun 15 '20 at 14:04