Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
add comment

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

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

4 Answers

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
add comment

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
add comment

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
add comment

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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.