SUMIF(left(A20:A40,2),"=11",D20:D40)?

[JRF]

I am trying to do an =SUMIF based on the first two charactors of a
column of data. In other words the data:


A B
== =
20 3
20 5
25 7
25 11

Would return 8 for the function =sumif(A:A,"=20",B:B) but if the data
were:

A B
==== =
20123 3
20232 5
25435 7
25454 11

I'd like to use a function something like =sumif(left(A:A,2),"=20",B:B)
and get the value 8 returned.

I'd rather not create a new column and extract the first two positions,
for reasons that are too long to go into.

Any ideas?

 

[Harlan Grove]

I am trying to do an =SUMIF based on the first two charactors of a
column of data. In other words the data:


A B
== =
20 3
20 5
25 7
25 11

Would return 8 for the function =sumif(A:A,"=20",B:B) but if the data
were:

A B
==== =
20123 3
20232 5
25435 7
25454 11

I'd like to use a function something like =sumif(left(A:A,2),"=20",B:B)
and get the value 8 returned.

....

SUMIF doesn't accept arrays as first or third arguments, only references to
single area ranges. Your choices are

=SUMPRODUCT((LEFT(A1:A100,2)="20")*B1:B100)

and

=SUMIF(A:A,">=20000",B:B)-SUMIF(A:A,">=21000",B:B)

if col A were numeric. If col A were text, you could use

=SUMIF(A:A,"20*",B:B)

 

[JRF]

Thanks for the reply. I wil test these and see what works.