Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have this table:

enter image description here

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

For example: SUM all amounts for LUxx

share|improve this question

migrated from stackoverflow.com Sep 16 '11 at 10:18

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

up vote 2 down vote accepted

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

share|improve this answer
    
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
1  
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!

share|improve this answer
    
+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).

share|improve this answer
    
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)
share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .