Below is a proposed solution for the challenge – Challenge 8 – Counting Number of single 1s in Rows in a Grid
Enter below Array formula –
=SUM(–(MMULT(–(A1:E10=1),TRANSPOSE(COLUMN(A1:E10)^0))=1))
Note – Array Formula is not entered by pressing ENTER after entering your formula but by pressing CTRL+SHIFT+ENTER. If you are copying and pasting this formula, take F2 after pasting and CTRL+SHIFT+ENTER. This will put { } brackets around the formula which you can see in Formula Bar. If you edit again, you will have to do CTRL+SHIFT+ENTER again. Don't put { } manually.
The worksheet related to this solution can be downloaded from Solution – Challenge 8 – Counting Number of single 1s in Rows in a Grid
I have another interesting solution for that:
=SUMPRODUCT((1)*(SUBTOTAL(9,OFFSET(A1:E10,ROW(A1:E10)-1,0,1))=1))
Hi, I've just discovered this website, really nice!!!! Thanks!