# How to solve error The resulting array was too large in MMUlLT formula with ~9700 rows

I need to calculate concatenation of the value and how many times it was used in rows as below: I tried to use this formula by closed-end references

`=ARRAYFORMULA(IF(LEN(L3:L9700),M3:M9700&" - "&MMULT((M3:M9700=TRANSPOSE(M3:M9700))*(ROW(M3:M9700)>=TRANSPOSE(ROW(M3:M9700))),SIGN(ROW(M3:M9700)))+0,IFERROR(1/0))) `

but it came out error message:

The resulting array was too large

Can advise how to solve this ?

NOTE: This is a follow-up of How to solve Error message in Google Sheet array formula: The resulting array was too large

• `mmult()` uses matrix multiplication and will only work up to 3162 rows, i.e., 10 million values in the matrix. Use `countifs()` as shown by Erik below to handle a larger number of rows. Aug 14, 2021 at 8:57

Delete `M3:M` and place the following in `M3`:

`=ArrayFormula(IF(L3:L="",,PROPER(L3:L)&" - "&COUNTIFS(LOWER(L3:L),LOWER(L3:L),ROW(L3:L),"<="&ROW(L3:L))))`

`PROPER` and `LOWER` are just used to assure that "boat" would match "Boat" and that the output would always be capitalized. The rest basically just asks for the count of matches up to and including each row.

If you really need ~9700 rows on each reference then it's very likely that you will need to use a different approach as 9700^2 = 94 090 000 (this is the number of cells required by MMULT in your formula) exceeds the array size limit.

• I do not think the five million cells per spreadsheet limit is relevant here. `mmult()` will handle ten million values in its matrix. That matrix is not saved anywhere so spreadsheet cell limits do not come into the play. Aug 14, 2021 at 9:01