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.

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


Thanks in advance


Joachim

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

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

--
HTH

Bob Phillips

"J1J" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
>
> --
> 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

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

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

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

--
HTH

Bob Phillips

"J1J" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
>
> --
> 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

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

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

--
HTH

Bob Phillips

"J1J" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
>
> --
> 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

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

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

--
HTH

Bob Phillips

"J1J" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
>
> --
> 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
>
> --
> 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

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

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

--
HTH

Bob Phillips

"J1J" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
>
> --
> 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

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

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

--
HTH

Bob Phillips

"J1J" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
>
> --
> 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

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

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

--
HTH

Bob Phillips

"J1J" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
>
> --
> 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
>
> --
> 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

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

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

--
HTH

Bob Phillips

"J1J" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
>
> --
> 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
>
> --
> 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

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

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

--
HTH

Bob Phillips

"J1J" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
>
> --
> 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>