SUM values in a column IF other column STARTS WITH (no equal digits)

[joostvanvlimmeren]

Hi,

I have 2 columns, A & B.
A is filled with 3 to 5 digit numbers (different business classes)
B are their corresponding amount of employees.
I want to sum all employees that are in the same category from A (type of business) but i only need the first 2 digits from column A. For example starting with the number 14.

I already tried things like: =SUMIF(A2:A28,"14*",B2:B282) but that don't seem to work. I got ms excel 2010. I also tried thinks with LEFT but me and excel are not really friends with that, it seems to fail over and over again.

Thanx for you time, this is really important for my research.

 

[ukmikeb]

Hi

Welcome to MrExcel Forum.

Perhaps SUMPRODUCT can step into the breach :-

=SUMPRODUCT(--(LEFT(A2:A282,2)+0=14),--(B2:B282))

hth

 

[joostvanvlimmeren]

Thanx for your quick reply. However excel says it contains an error..

 

[ukmikeb]

=SUMIF(A2:A28,"14*",B2:B282)

Perhaps I elaborated too much on your OP. Could it be :-

=SUMPRODUCT(--(LEFT(A2:A28,2)+0=14),--(B2:B28))

The ranges in the SUMPRODUCT have to be equal length!

Otherwise, what is the error you are getting? My clairvoyancy skills seem to have left me today.

 

[joostvanvlimmeren]

For the second time it says:
"The formula you typed contains an error."
Should i try an older excel version?

Thanx for you time btw

 

[Aladin Akyurek]

=SUMPRODUCT(--(LEFT(A2:A282&"00",2)+0=14),B2:B282)

should succeed. By the way, you might need to change comma to a semi-colon on an European system.

 

[joostvanvlimmeren]

=SUMPRODUCT(--(LEFT(A2:A282&"00",2)+0=14),B2:B282)

should succeed. By the way, you might need to change comma to a semi-colon on an European system.

many thanx!! it was indeed the simpel change of comma to semi-colon