CODEWITHSUNDEEP
excelexcel-formulaoffice365
Whitmbk
Whitmbk

Reputation: 11

Array function to sum multiple columns that have a value in cell right of the column matching criteria in Excel 365

I have a table that has 2 columns per month extending for 3 years. The first column of the month shows the invoice number and 2nd shows the amount. There are situations where the invoice Number is the same for multiple months, thus want to sum up any columns that match a particular invoice#. Is this possible in the provided format? I'm thinking I can use a Sumproduct and offset combination in some sort of format? I'm new to these functions.

Data table

Invoice Month1 Invoice Month 2
KTL3017 $2,700,000.00 KTLO3018 $1,500,000.00
KTL3020 $115,000.00 KTL3020 $200,000.00
KTLO127 $75,000.00 KTLO129 $25,000.00

Results

Invoice Amount
KTL3017 $2,700,000.00
KTL3018 $1,500,000.00
KTL3020 $315,000.00
KTLO127 $75,000.00
KTLO129 $25,000.00

Upvotes: 1

Views: 60

Answers (2)

Harun24hr
Harun24hr

Reputation: 37050

Lets try few simple steps-

=XLOOKUP(A10:A14,A2:A4,B2:B4,0)+XLOOKUP(A10:A14,C2:C4,D2:D4,0)

Or using GROUPBY().

=GROUPBY(VSTACK(A2:A4,C2:C4),VSTACK(B2:B4,D2:D4),SUM,0,0)

Input data:

Invoice Month1 Invoice Month 2
KTL3017 $2,700,000.00 KTLO3018 $1,500,000.00
KTL3020 $115,000.00 KTL3020 $200,000.00
KTLO127 $75,000.00 KTLO129 $25,000.00

Output:

Invoice Amount
KTL3017 $2,700,000.00
KTLO3018 $1,500,000.00
KTL3020 $315,000.00
KTLO127 $75,000.00
KTLO129 $25,000.00

enter image description here

Upvotes: 1

Mayukh Bhattacharya
Mayukh Bhattacharya

Reputation: 27438

Here is one way to accomplish the desired output, using GROUPBY() function:

enter image description here

• Formula used in cell A6

=LET(
     α, WRAPROWS(TOCOL(Data_Table),2),
     GROUPBY(TAKE(α,,1),DROP(α,,1),SUM,,0,-2))

If dont have access to GROUPBY() then another possible alternative method which works with MS365:

=LET(
     a, WRAPROWS(TOCOL(Data_Table),2),
     b, TAKE(a,,1),
     c, UNIQUE(b),
     d, MAP(c,LAMBDA(x,SUM((x=b)*DROP(a,,1)))),
     HSTACK(c,d))

Per OP's comments:

I guess I needed to clarify the Results table. I have a Results table where the invoice # is manually entered in Column A, and need Column B to summarize all invoices related to column A from table 1.

enter image description here

• Formula used in cell B12

=MAP(A12:A16,LAMBDA(x, 
 LET(y, WRAPROWS(TOCOL(Data_Table),2), 
 SUM((TAKE(y,,1)=x)*DROP(y,,1)))))

Or, can use the following as well:

=SUM(IFERROR((A$2:C$4=A12)*B$2:D$4,0))

Upvotes: 2

Related Questions