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

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

-

## migrated from stackoverflow.comSep 16 '11 at 10:18

This question came from our site for professional and enthusiast programmers.

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

-
Thank you and I'm sorry, it was stupid question. Just divide column A to two colums (`LEFT (A;2)` and `RIGHT(A;2)`) and make contingency table:-) hurry slowly.... – gaffcz Sep 16 '11 at 9:51
There is a more easier way to do this, check my answer – Robby Shaw Sep 16 '11 at 10:06

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!

-
+1 simplest approach. – brettdj Sep 20 '11 at 13:59

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

-
Thank you and I'm sorry, it was stupid answer. Just divide column A to two colums (`LEFT (A;2)` and `RIGHT(A;2)`) and make contingency table:-) hurry slowly.... – gaffcz Sep 16 '11 at 9:52

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)
``````
-