EXCEL - SUM column grouped by part of string of other column

Question

I have this table:

How to sum all amount in column B for the first two chars in column A?

For example: SUM all amounts for LUxx

Answer 1

You can look into the SUMIF function in conjunction with LEFT() which will enable you to get the first two characters.

Answer 2

SUMIF can do this. You can try the below function (I already tested):

=SUMIF(A2:A20,"LU*",B2:B20)

• A2:A20 is the range you want to look into
• "LU"* means you only want the records which start with LU in A2:A20
• B2:B20 is the actual range that you use to sum

Don't forget to change the number in the range according to your Excel content!

Answer 3

To use the SUMIF approach, add an intermediate column (say column C) with formula:

=LEFT(A2,2)

and copy down for each row used. Then use:

=SUMIF(C2:C19,"LU",B2:B19)

Answer 4

I couldn't make diagonalbatman's solution work (I don't think that SUMIF do accept formula).

Here is a SUMPRODUCT formula solution:

=SUMPRODUCT(B1:B24*IF(LEFT(A1:A24;2)="LU",1,0))

May have to validate with CtrlShiftEnter (should work without but sometimes, it isn't well interpreted by SUMPRODUCT).