Array Formula breaks when trying referencing another array formula value output

Chad O. 0
Viewed 93

I have attached a copy of a sheet I'm working in.

What I'm trying to do is build an array formula that outputs an "Overall Total" value in column J, which is the sum of what Column E cell that aligns with the row the formula is being calculated in plus the next 3 cells below in column E only when 2 conditions are both met. Each condition is looking at a particular Column and trying to match the cell of that column in questions plus and the next 3 cells below it to all match.

Knowing that I can't directly do IF/IFS with "And" or "Or" in an array formula. I have created Column F "Date Match" to check for the first condition in column D "Date" and output 1 or 0 as a true/false if the 4 cells in question match, and Column H "Scan Match" to check the 2nd condition of column C "Scan" and output 1 or 0 for true/false if the 4 cells in questions match.

I have the array formula in column J working, but only if I use a non-array formula in column F and column H. IF F or H are set up in an array, it breaks my formula in column J.

I need columns F, H, and J in an array because this sheet receives 16 new data entries from the google form it's linked to per day and then that get multiplied many times over base on how many locations we have. So, having to come in and drag down rows is not feasible.

How can I either get column F and H in an array the doesn't break Column J or create an array formula in column J that checks for the 2 conditions and returns the value if true or leaves the cell blank if false? Any help or suggestions would be greatly appreciated!

enter image description here

byrow array-formulas google-sheets-formula google-sheets
2 Answers

Use this formula
Since I am aware that you want a formula that does not require movement, here it is.

Paste it F2

With Arrayformula

=ArrayFormula(LAMBDA(vv,s, IF(s<>1,,VLOOKUP(vv, 
                                  { QUERY(UNIQUE(vv), " Select * where Col1 is not null "),
                                    BYROW(QUERY(UNIQUE(vv), " Select * where Col1 is not null "), LAMBDA(c, SUMIF(vv, "="&c, E2:E)))}, 2,1)))(
              ARRAYFORMULA(LAMBDA(u, LAMBDA(l, IF(A2:A="",,VLOOKUP(ROW(E2:E),FILTER({ROW(E2:E),l},l<>""),2,TRUE)))(IF(u="",,COUNTIFS(u, "<>", ROW(E2:E), "<="&ROW(E2:E)))))(ArrayFormula(IF(
 IF(A2:A="",, { IF(D2:D="",,BYROW(
 VLOOKUP(ROW(D2:D), 
         { ArrayFormula(ROW(D2:D)),BYROW(ROW(D2:D), LAMBDA(x, TEXTJOIN(CHAR(10), , BYROW(SEQUENCE(4,1,x), LAMBDA(v, FILTER(D2:D, ROW(D2:D)=v))))))}, 2,0), 
 LAMBDA(g, LAMBDA(r, SUMPRODUCT(BYROW( r, LAMBDA(v, lambda(x,y,INDEX(x, y))(r,1)=v)))=4)(TRANSPOSE(SPLIT(g, CHAR(10)))))))*
                IF(C2:C="",, BYROW(
 VLOOKUP(ROW(C2:C), 
         { ArrayFormula(ROW(C2:C)),BYROW(ROW(C2:C), LAMBDA(x, TEXTJOIN(CHAR(10), , BYROW(SEQUENCE(4,1,x), LAMBDA(v, FILTER(C2:C, ROW(C2:C)=v))))))}, 2,0), 
 LAMBDA(g, LAMBDA(r, SUMPRODUCT(BYROW( r, LAMBDA(v, lambda(x,y,INDEX(x, y))(r,1)=v)))=4)(TRANSPOSE(SPLIT(g, CHAR(10)))))))})<>1,,1)))),
              ArrayFormula(IF(A2:A="",, { IF(D2:D="",,BYROW(
 VLOOKUP(ROW(D2:D), 
         { ArrayFormula(ROW(D2:D)),BYROW(ROW(D2:D), LAMBDA(x, TEXTJOIN(CHAR(10), , BYROW(SEQUENCE(4,1,x), LAMBDA(v, FILTER(D2:D, ROW(D2:D)=v))))))}, 2,0), 
 LAMBDA(g, LAMBDA(r, SUMPRODUCT(BYROW( r, LAMBDA(v, lambda(x,y,INDEX(x, y))(r,1)=v)))=4)(TRANSPOSE(SPLIT(g, CHAR(10)))))))*
                IF(C2:C="",, BYROW(
 VLOOKUP(ROW(C2:C), 
         { ArrayFormula(ROW(C2:C)),BYROW(ROW(C2:C), LAMBDA(x, TEXTJOIN(CHAR(10), , BYROW(SEQUENCE(4,1,x), LAMBDA(v, FILTER(C2:C, ROW(C2:C)=v))))))}, 2,0), 
 LAMBDA(g, LAMBDA(r, SUMPRODUCT(BYROW( r, LAMBDA(v, lambda(x,y,INDEX(x, y))(r,1)=v)))=4)(TRANSPOSE(SPLIT(g, CHAR(10)))))))}))))

enter image description here

Sources

Notes
If you want to delve deep, consider looking at the previous edit.

Let's make it a named function (˘◡˘ )

See how to using Google Sheets new formulas

enter image description here

Paste this in formula definition

=ArrayFormula(
  LAMBDA(
    vv,
    s,
    IF(
      s <> 1,
      ,
      VLOOKUP(
        vv,
{            QUERY(
              UNIQUE(
                vv
              ),
              "Select*whereCol1isnotnull"
            ),
            BYROW(
              QUERY(
                UNIQUE(
                  vv
                ),
                "Select*whereCol1isnotnull"
              ),
              LAMBDA(
                c,
                SUMIF(
                  vv,
                  "=" & c,
                  range
                )
              )
            )},
        2,
        1
      )
    )
  ) (
    ARRAYFORMULA(
      LAMBDA(
        u,
        LAMBDA(
          l,
          IF(
            key = "",
            ,
            VLOOKUP(
              ROW(
                range
              ),
              FILTER(
{                    ROW(
                      range
                    ),
                    l},
                l <> ""
              ),
              2,
              TRUE
            )
          )
        ) (
          IF(
            u = "",
            ,
            COUNTIFS(
              u,
              "<>",
              ROW(
                range
              ),
              "<=" &
              ROW(
                range
              )
            )
          )
)
      ) (
        ArrayFormula(
          IF(
            IF(
              key = "",
              ,
{                  IF(
                    date = "",
                    ,
                    BYROW(
                      VLOOKUP(
                        ROW(
                          date
                        ),
{                            ArrayFormula(
                              ROW(
                                date
                              )
                            ),
                            BYROW(
                              ROW(
                                date
                              ),
                              LAMBDA(
                                x,
                                TEXTJOIN(
                                  CHAR(
                                    10
                                  ),
                                  ,
                                  BYROW(
                                    SEQUENCE(
                                      4,
                                      1,
                                      x
                                    ),
                                    LAMBDA(
                                      v,
                                      FILTER(
                                        date,
                                        ROW(
                                          date
                                        ) = v
                                      )
                                    )
                                  )
                                )
                              )
                            )},
                        2,
                        0
                      ),
                      LAMBDA(
                        g,
                        LAMBDA(
                          r,
                          SUMPRODUCT(
                            BYROW(
                              r,
                              LAMBDA(
                                v,
                                lambda(
                                  x,
                                  y,
                                  INDEX(
                                    x,
                                    y
                                  )
                                ) (
                                  r , 1
) = v
                              )
                            )
                          ) = 4
                        ) (
                          TRANSPOSE(
                            SPLIT(
                              g,
                              CHAR(
                                10
                              )
                            )
                          )
)
                      )
                    )
                  ) *
                  IF(
                    scan = "",
                    ,
                    BYROW(
                      VLOOKUP(
                        ROW(
                          scan
                        ),
{                            ArrayFormula(
                              ROW(
                                scan
                              )
                            ),
                            BYROW(
                              ROW(
                                scan
                              ),
                              LAMBDA(
                                x,
                                TEXTJOIN(
                                  CHAR(
                                    10
                                  ),
                                  ,
                                  BYROW(
                                    SEQUENCE(
                                      4,
                                      1,
                                      x
                                    ),
                                    LAMBDA(
                                      v,
                                      FILTER(
                                        scan,
                                        ROW(
                                          scan
                                        ) = v
                                      )
                                    )
                                  )
                                )
                              )
                            )},
                        2,
                        0
                      ),
                      LAMBDA(
                        g,
                        LAMBDA(
                          r,
                          SUMPRODUCT(
                            BYROW(
                              r,
                              LAMBDA(
                                v,
                                lambda(
                                  x,
                                  y,
                                  INDEX(
                                    x,
                                    y
                                  )
                                ) (
                                  r , 1
) = v
                              )
                            )
                          ) = 4
                        ) (
                          TRANSPOSE(
                            SPLIT(
                              g,
                              CHAR(
                                10
                              )
                            )
                          )
)
                      )
                    )
                  )}
            ) <> 1,
            ,
            1
          )
        )
)
    ) ,
    ArrayFormula(
      IF(
        key = "",
        ,
{            IF(
              date = "",
              ,
              BYROW(
                VLOOKUP(
                  ROW(
                    date
                  ),
{                      ArrayFormula(
                        ROW(
                          date
                        )
                      ),
                      BYROW(
                        ROW(
                          date
                        ),
                        LAMBDA(
                          x,
                          TEXTJOIN(
                            CHAR(
                              10
                            ),
                            ,
                            BYROW(
                              SEQUENCE(
                                4,
                                1,
                                x
                              ),
                              LAMBDA(
                                v,
                                FILTER(
                                  date,
                                  ROW(
                                    date
                                  ) = v
                                )
                              )
                            )
                          )
                        )
                      )},
                  2,
                  0
                ),
                LAMBDA(
                  g,
                  LAMBDA(
                    r,
                    SUMPRODUCT(
                      BYROW(
                        r,
                        LAMBDA(
                          v,
                          lambda(
                            x,
                            y,
                            INDEX(
                              x,
                              y
                            )
                          ) (
                            r , 1
) = v
                        )
                      )
                    ) = 4
                  ) (
                    TRANSPOSE(
                      SPLIT(
                        g,
                        CHAR(
                          10
                        )
                      )
                    )
)
                )
              )
            ) *
            IF(
              scan = "",
              ,
              BYROW(
                VLOOKUP(
                  ROW(
                    scan
                  ),
{                      ArrayFormula(
                        ROW(
                          scan
                        )
                      ),
                      BYROW(
                        ROW(
                          scan
                        ),
                        LAMBDA(
                          x,
                          TEXTJOIN(
                            CHAR(
                              10
                            ),
                            ,
                            BYROW(
                              SEQUENCE(
                                4,
                                1,
                                x
                              ),
                              LAMBDA(
                                v,
                                FILTER(
                                  scan,
                                  ROW(
                                    scan
                                  ) = v
                                )
                              )
                            )
                          )
                        )
                      )},
                  2,
                  0
                ),
                LAMBDA(
                  g,
                  LAMBDA(
                    r,
                    SUMPRODUCT(
                      BYROW(
                        r,
                        LAMBDA(
                          v,
                          lambda(
                            x,
                            y,
                            INDEX(
                              x,
                              y
                            )
                          ) (
                            r , 1
) = v
                        )
                      )
                    ) = 4
                  ) (
                    TRANSPOSE(
                      SPLIT(
                        g,
                        CHAR(
                          10
                        )
                      )
                    )
)
                )
              )
            )}
      )
    )
)
)

using Google Sheets new formulas

How to use

Create and use named functions

Used functions: Checkout Google Sheets function list
ARRAYFORMULA - LAMBDA - IF - VLOOKUP - QUERY - UNIQUE - BYROW - SUMIF - ROW

Osm0

This method combines your date match, scan match, and total column into one array formula.

=ArrayFormula(((D2:D=D3:D)*(D3:D=D4:D)*(D4:D=D5:D)*(C2:C=C3:C)*(C3:C=C4:C)*(C4:C=C5:C))*(E2:E+E3:E+E4:E+E5:E))

Since you need Date Match to be true and Scan Match to be true, you can combine those two columns using the same multiplication method you've already used. Then you multiply that value (it will be 0 if false and 1 if true) by the sum of the current row and the next three rows in column E (like you've already done in the Overall Total column) to get the total. I've included the ISBLANK test to prevent 0s from populating the full length of the column.

enter image description here

LASheets0
Related