Search code examples
excelexcel-formulaconcatenationsumifs

Excel - Concatenating column a and column b and summing column c

I'm trying to write a formula in Excel that would essentially concatenate the text strings in Column A and Column b, get the unique combinations for (Column A + Column B), and then get the sum of Values from Column C that are associated with that unique combination of Column A + Column B

So for example below:

A B C
John Smith 3
John Beky 4
John Smith 7
Alice Smith 6
Alice " " 9
Alice Smith 2

Hoping to get the following result below:

D E
John Smith 10
John Beky 4
Alice Smith 8
Alice " " 9

I got the CONCATENATED values on Column D through this:

=SORT(UNIQUE(CONCATENATE(A1:A6, B1:B6)), 1, -1)

but can't seem to get the associated values for Column E

Currently trying this formula for Column E:

=SUMIF((CONCATENATE(A1:A6, B1:B6)), D1, C1:C6)

but it seems that I can't use CONCATENATE and SUM together in excel?

Is that correct?

Is there a better way to handle this?

Solution

  • Here is one way of doing this:

    enter image description here

    =LET(
     _Names, A1:A6&" "&B1:B6,
     SORT(UNIQUE(HSTACK(_Names,MMULT(N(_Names=TOROW(_Names)),TAKE(C1:C6,,-1)))),,-1))

    Also if applicable can use GROUPBY()

    =GROUPBY(A1:A6&" "&B1:B6,C1:C6,SUM,,0,-1)

    Or, using BYROW()

    =LET(
 _Names, A1:A6&" "&B1:B6,
 _Uniq, UNIQUE(_Names),
 SORT(HSTACK(_Uniq, BYROW(_Uniq, LAMBDA(x, SUM((x=_Names)*C1:C6)))),1,-1))