trouble combining array formula and sumproduct (to replace sumif)

Question

I have this 2 google spreadsheet where one of them contain whole data and the other act as a dashboard.

in the dashboard sheet, i would like to show a sum of values in data sheet using this fuction:

=ARRAYFORMULA(IF(ISBLANK(A9:A),,SUMPRODUCT(IMPORTRANGE("1GuDE-oYK0u9hZBSqRM4EKevW0_wl4ssDVuRedlyj0TA","not deleted!L2:L"),IMPORTRANGE("1GuDE-oYK0u9hZBSqRM4EKevW0_wl4ssDVuRedlyj0TA","not deleted!B2:B")=A9:A)))

the A9:A is where the list of data would be used as criterion of the sum in sumproduct.

but the result is

Array arguments to EQ are of different size.

is there any way that i can use the A9:A as criterion in sumproduct?

Answer 1

The SUMPRODUCT function is an aggregate function. If you want to perform a conditional sum by row using SUMPRODUCT, you have to use MAP instead of ARRAYFORMULA.

=MAP(A9:A,
   LAMBDA(a,
     IF(ISBLANK(a),,
        SUMPRODUCT(
          IMPORTRANGE("1GuDE-oYK0u9hZBSqRM4EKevW0_wl4ssDVuRedlyj0TA","not deleted!L2:L"),
          IMPORTRANGE("1GuDE-oYK0u9hZBSqRM4EKevW0_wl4ssDVuRedlyj0TA","not deleted!B2:B")=a))))