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!
dsauce
  • 592
  • 2
  • 14
  • 36

1 Answers1

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

Harun24hr
  • 30,391
  • 4
  • 21
  • 36
  • 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. – dsauce May 20 '21 at 03:04
  • 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 – dsauce May 20 '21 at 03:17
  • Should work. Can you share a sample workbook via google drive or dropbox? I can't assume your figure. – Harun24hr May 20 '21 at 03:21
  • 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. – dsauce May 20 '21 at 03:26
  • Yes. I made mistake. I will amend my answer. – Harun24hr May 20 '21 at 03:27