Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 -- J1J ------------------------------------------------------------------------ J1J's Profile: http://www.excelforum.com/member.php...o&userid=24668 View this thread: http://www.excelforum.com/showthread...hreadid=382436 |
#2
|
|||
|
|||
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. 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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can wildcards be used in SUMPRODUCT conditions | Excel Worksheet Functions | |||
Wildcards in Functions | Excel Worksheet Functions | |||
Wildcards in Functions | Excel Worksheet Functions |