Home Extracting Sum of Various Columns based on value in other Columns in a repetitive array of Data

Questions

Extracting Sum of Various Columns based on value in other Columns in a repetitive array of Data

February 28, 2024

I need to find the sum of all matching items in the following data for which sumif() is going to be very lengthy and need a shorter way of doing it

The data structure is as follows:

Here there are 20 items and hence a set of 4 columns repeats for every item.

Now, I want to get the total of items example
Item CA30 Total Item Qty = 10
Item CA10 Total Item Qty = 200

The new table should look like: (please ignore #N/A)

Link to sample sheet

>Solution :

Here’s a possible solution:

=LET(data,REDUCE(TOCOL(,1),SEQUENCE(20,1,1,5),
             LAMBDA(a,i,VSTACK(a,CHOOSECOLS(Data!E2:CZ,SEQUENCE(2,1,i,3))))),
     MAP(B2:B37,LAMBDA(code,SUMPRODUCT(INDEX(data,,1)=code,INDEX(data,,2)))))

The first part of the formula is arranging the data in a usable format then we are performing a simple conditional sum using the SUMPRODUCT function.