arraysgoogle-sheetsmaxifs

MAXIFS + Array Issue

It's my first time posting in Stack Overflow so I do apologize if there are any format or etiquette issues.

My array formula is not behaving as an actual array formula. It's just giving me results on the first cell, and then it does not autopopulate the lower cells.

These are the details

  1. RawDataOrdenes = A sheet where new orders are posted. I have an order number and a receipt number in column C and D

  2. RawDataActualizaciones = A sheet where updates of the orders are posted. I also have the order and receipt number, in column D and E, respectively. I also have a field where the date of the order creation is placed.

  3. Control de Orden = A sheet that both sheets feed into. The new orders are added and updated if the Actualizaciones sheet changes. I also have a field whewre the date of the updates are reflected. Note there could be multiple updates per order.

The problem is this. I'm creating a field where I can reflect the date of the last update. For this, I am using the following formula:

=arrayformula(MAXIFS(RawDataActualizacion!G2:G,RawDataActualizacion!B2:B,(D2:D&C2:C)))

Ranges are:

  1. G2:G = Date of last update
  2. B2:B = Concat of Receipt and Order number
  3. D2:D&C2:C = Concat of Receipt and Order number

Please note - it does return the right value on the first cell, but it does not auto populate the lower cells. So, it does not actually work as an Array formula.

Any help would be appreciated.

Thanks in advance

**Edit:

I have already tried these two formulas:

=arrayformula(BYROW(RawDataActualizacion!G2:G,LAMBDA(x,MAXIFS(RawDataActualizacion!B2:B,(D2:D&C2:C),x))) )

and

=BYROW(A3:INDEX(RawDataActualizacion!G2:G,COUNTA(RawDataActualizacion!G2:G)),LAMBDA(x,MAXIFS(RawDataActualizacion!B:B,(D2:D&C2:C),x)))

I created a helper column with the concatenate as an array (D2:D&C2:C) on cells J2:J

When trying this one out:

=arrayformula(BYROW(RawDataActualizacion!G2:G,LAMBDA(x,MAXIFS(RawDataActualizacion!B2:B,(D2:D&C2:C),x))) )

It does work (and auto populate) but I get zeroes.

Here's the link if needed: https://docs.google.com/spreadsheets/d/1JdnCSOJsdgcZ8l4recxVEbJJt4rOBVPrOSceAGwQ-OU/edit?usp=sharing

Solution

  • Try this formula in Cell K2 of Control de Orden tab

    =map(J2:J,lambda(z,if(z="",,let(a,maxifs(RawDataActualizacion!G:G,RawDataActualizacion!B:B,z),if(a=0,,a)))))

    enter image description here