0

I have a 2D dynamic array of 0s and 1s (e.g. B1#). I want to calculate the number of 1s in each row. This can be done using SUM of each row or COUNTIFS on each row.

My goal is to have a single dynamic array formula which would sum/countifs each row and hence spill automatically according to the number of rows in the 2D dynamic array.

How do I do this?

What I tried:

  • I added a support column in A1# as Sequence(Rows(B1#))
  • I tried SUM(INDEX(B1#,A1#,0)) but obviously this doesn't work because SUM is bound to return a single value while I am expecting a spill of totals across ROWS(B1#)
  • I tried COUNTIFS(INDEX(B1#,A1#,0),1) but this also results in a single value that too #VALUE!
Improve this question
4
  • Can you put few sample data? Do you have multiple column in each row? Or only single column B#? Commented May 20, 2021 at 2:29
  • Yes, multiple columns, as I mentioned it is a 2D array with only 0s and 1s. Nothing much to the data. Commented May 20, 2021 at 2:46
  • But you used only one column in your formula. Commented May 20, 2021 at 2:47
  • A1#=Sequence(Rows(B1#)) so that is a single column, 1D array. B1# is a 2D array, it has several columns and it can still be referred as B1# :) Commented May 20, 2021 at 2:56

1 Answer 1

Reset to default
2

MMULT() with SEQUENCE() may work for you. Give a try on below formula.

=MMULT(B1:E14,SEQUENCE(COLUMNS(B1:E14),,,0))

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Ah! I didn't think of MMULT. Thanks. I tried this, it works in principle like you did in the screenshot. But I am not sure why it is not working in my actual table and just gives me #VALUE. Let me check.
I checked and figured that MMULT is only working for a max 2x2 matrix for any more rows or columns, MMULT results in #VALUE. Do you know why could it be limited for me whilst you're able to do it for 4x4? Anyhow, my 2D arrays have thousands of rows and I am sure MMULT won't work in this case
Should work. Can you share a sample workbook via google drive or dropbox? I can't assume your figure.
All resolved now. There is a mistake in the formula you've shown in the screenshot. It happens to work because n(rows)=n(cols) in your example. It should be SEQUENCE(COLUMNS(B1:E4),,,0). For MMULT --> n(array 1 cols) = n(array 2 rows) . Please correct it so that I can mark it as answer.
Yes. I made mistake. I will amend my answer.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.