excelexcel-formula

SUMIF on two arrays from FILTER-function

0 A B C D E F G H I J K L
1 Brand A Product 01 600 Brand A Product 01 370 Brand C
2 Brand A Product 02 100 Brand C Product 02 650
3 Brand A Product 02 300 Brand C Product 01 390 Product 02 200 Product 02 650
4 Brand B Product 01 400 Product 02 800 Product 01 390
5 Brand B Product 03 500 Product 04 900
6 Brand C Product 02 200 Product 01 700
7 Brand C Product 02 800
8 Brand C Product 04 900
9 Brand C Product 01 700 Product 02 350
10 Brand D Product 03 250 Product 04 900
11 Brand D Product 03 460 Product 01 310
12 Brand D Product 04 690

In the table above I have two different lists:

List A Column A:C
List B Column E:G

In Range I3:J6 and Range K3:L4 I filter these two lists based on the criteria in Cell I1 using the following formulas:

Cell I3 =CHOOSECOLS(FILTER(A1:C12,A1:A12=I1),2,3) 
Cell K3 =CHOOSECOLS(FILTER(E1:G12,E1:E12=I1),2,3)

All this works fine.

Now my target is the list in Range I9:J11.
The list uniquely displays all products from the filtered list A in Range I3:J6 and subtracts the corresponding values per product from the filtered list B in Range K3:L4.

I know I could do this by applying the UNIQUE function and the SUMIF function on the existing results in the Range I3:J6 and Range K3:L4.

However, I would prefer to have a solution in which I do not need Range I3:J6 and Range K3:L4.
Instead the list in Range I9:J11 should be displayed without the "helper ranges".

Do you have any idea how to achieve this?

Note:

  1. List B will never contain values that do not exist in List A. Neither for brand nor for product.

  2. List B can also contain multiple times the same product per brand. It is not always unique. For example it can happen that Product 02 appears x-times per Brand C.

Solution

  • GROUPBY() may be simpler way. Try-

    =LET(x,VSTACK(HSTACK(E1:F3,G1:G3*(-1)),A1:C12),GROUPBY(CHOOSECOLS(x,2),CHOOSECOLS(x,3),SUM,0,0,,CHOOSECOLS(x,1)=I1))

    You may also try-

    =LET(p,UNIQUE(VSTACK(FILTER(B:B,A:A=I1),FILTER(F:F,E:E=I1))),
HSTACK(p,MAP(p,LAMBDA(x,SUMIFS(C:C,B:B,x,A:A,I1)-SUMIFS(G:G,F:F,x,E:E,I1)))))

    enter image description here