0

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!

Arno B
  • 1

2 Answers2

1

You may try:

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

enter image description here

rockinfreakshow
  • 15,077
  • 3
  • 12
  • 19
1

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)))

enter image description here

Harun24hr
  • 30,391
  • 4
  • 21
  • 36