Google Sheets - Using MAXIFS within ArrayFormula

Question

I'm reaching out because I'm facing an issue in Google Sheets for which I can't find a solution and I'm certain someone here might have an answer to save me.

The situation is actually quite simple :

• In the D column of my sheet, I have a list of email addresses ;
• In the H column, for each of these addresses, I have a number of orders ;
• In the I column, I am trying to get, for the email address indicated in the D column, the maximum number of orders such as indicated in the H column.

See a screenshot of the file here

Of course, this can really simply be done with the MAXIFS() formula such as follows :

=maxifs(H:H;D:D;DX) where X is the n° of the lign.

Now, the issue appears when I try to transform this formula to an "ArrayFormula".

When trying to transform it, I tried typing :

=arrayformula(maxifs(H:H;D:D;D:D))

But it doesn't "expand" to the following lines. The formula works for the line in which it is typed, but I can't get it to "replicate".

Does anyone here have an idea of how to solve this issue?

I wish you all a very pleasant week!

Answer 1

You may try:

=map(D3:D,lambda(z,if(z="",,vlookup(z,sort({D3:D,H3:H},2,0),2,))))

Answer 2

Use MAXIFS() with BYROW() to make it dynamic. Try-

=BYROW(A3:A9,LAMBDA(x,MAXIFS(B3:B,A3:A,x)))

To refer full column as input parameter, use-

=BYROW(A3:INDEX(A3:A,COUNTA(A3:A)),LAMBDA(x,MAXIFS(B3:B,A3:A,x)))