Problems with SUMIF function and Wildcards (* and ?)
 

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.
:confused:

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


Thanks in advance


Joachim


--
J1J
------------------------------------------------------------------------
J1J's Profile: http://www.excelforum.com/member.php...o&userid=24668
View this thread: http://www.excelforum.com/showthread...hreadid=382436

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

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

--
HTH

Bob Phillips

"J1J" wrote in message
...

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.
:confused:

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


Thanks in advance


Joachim


--
J1J
------------------------------------------------------------------------
J1J's Profile:
http://www.excelforum.com/member.php...o&userid=24668
View this thread: http://www.excelforum.com/showthread...hreadid=382436

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


--
J1J
------------------------------------------------------------------------
J1J's Profile: http://www.excelforum.com/member.php...o&userid=24668
View this thread: http://www.excelforum.com/showthread...hreadid=382436

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

--
HTH

Bob Phillips

"J1J" wrote in message
...

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


--
J1J
------------------------------------------------------------------------
J1J's Profile:
http://www.excelforum.com/member.php...o&userid=24668
View this thread: http://www.excelforum.com/showthread...hreadid=382436

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

Bob

"J1J" wrote in message
...

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


--
J1J
------------------------------------------------------------------------
J1J's Profile:
http://www.excelforum.com/member.php...o&userid=24668
View this thread: http://www.excelforum.com/showthread...hreadid=382436