Turning off wildcard characters??

[Steve_Courts]

Hi Everyone

I am using a SUMIF formula to analyse phone extensions (for example):

=SUMIF(Range,$B2&$C2&D$1,SumRange)

My problem is that valid phone extensions are 7**## or 7****...

When it performs the SUMIF calculation it treats the * and # as a wildcard and includes values from other phone extensions (e.g. 71245) in the sum.

Is there a way to avoid this? I've tried variations using TEXT() and "" and CONCATENATE etc...

I was intially using VLOOKUP formulas but this was becoming too unweildy.

Any ideas??

Cheers
Steve

[NBVC]

if you precede each asterisk or other wildcard with a tilde ~, it cancels the wildcard and looks for the specific character.

I am not sure what exactly is in each of B2, C2 and D1, but if there are several asterisks, etc in any of those cells, you can incorporate substitute function to replace each * or other character with a ~*, etc.

e.g.

=SUMIF(Range,Substitute($B2&$C2&D$1,"*","~*"),SumRange)

other character substitutions can be nested within the above Substitute function too.