Problems with SUMIF function and Wildcards (* and ?)

[J1J]

Hey

I have a hard time getting this to work.

I wanted to make a sum of different accounts taking only the 2 first
account numbers. Using a formula like this one.

SUMIF(A8:A179;"14*";G8:G179), For all the accounts in the range
beginning with 14.

But in stead of looking for the * as a wildcard it actually looks for
14* (and I didnt type ~*); thus naturally giving me a sum of 0.

The strange thing is that on another file I received from a colleague
it works perfectly on the collumns he created, but not on the new
columns I created in his file.

I have tried copying his formating but it didnt change anything.


I am currently using Windows 2003, in english on a french computer.


Thanks in advance


Joachim

 

[Bob Phillips]

Could it be that they are actually numbers not text? Try

=SUMPRODUCT(--(LEFT(A8:A179,2)="14"),G8:G179)

 

[J1J]

Thanks great that really helped, once I changed the formating of your
formula a bit apparently on my version , has to be replaced by ;



Thanks again


Joachom

 

[Bob Phillips]

OK, you have a continental version of Excel. If you post again, mention
that, we might give a more correct formula

 

[Bob Phillips]

or we (I) could learn to look more carefully at the formula given :-(

Bob