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
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.