EDIT: below is simply a reiteration / embellished version of dll's prior post...
In relation the previously referred to thread: http://www.excelforum.com/excel-work...-contains.html
I suspect the issue is because by XXXX you are actually referring to other digits rather than alpha characters... ie each entry in the range is a number, correct ?
(on the above thread I had presumed (incorrectly) otherwise so apologies on my part for that)
If that's the case then searching for a text string within a range of numbers will always return 0, eg:
A1:A4
1234
4123
1231
2231
will return 0 given all of the above values are in fact numbers and not strings whereas the SUMPRODUCT approach:
works and returns 3 because it evaluates each entry as a string by means of the SEARCH function.
So in essence it's an issue of data type when it comes to the COUNTIF/SUMIF approaches... your test should use an operator appropriate to the data type of the criteria range, eg:
If per your earlier thread you were concerned only with the three left most digits and your numbers were always of a consistent length (key) as per the example here - you could use two COUNTIF functions (or one COUNTIFS if using XL2007) eg:
in these instances however I would say that you're best served using the SUMPRODUCT as it's far more flexible (if potentially more "expensive")...
If performance is a real concern I would say that you would then be best served creating an adjacent column to your numbers that converts them to strings, eg:
at which point
etc will work irrespective of "number" length etc etc... ie you can use SUMIF/COUNTIF with wildcards without issue.
Does that help clarify things ?
Bookmarks