Case Sensitive Counting & Filtering
 

I have a huge database in wich some fields are strings. I need to count
records based - amongst others - on the two last characters of a field.
However, I must differentiate between az, aZ, AZ etc.

The COUNTIF does not allow for that (Right?).
I can circumvent the COUNTIF through using SUMPRODUCT, e.g.

SUMPRODUCT((ISNUMBER(FIND("aZ",RIGHT(Field1,2)))*( Field2=condition)))

I have the impression that Excel becomes very slow by using this.

So far for the COUNT; but I would liek as well to filter specific records on
the database with e.g. the Advanced Filter, how do I enter a criterion to
filter out records with last 2 characters "aZ" (and not extract "AZ" and so
on)?

Thanks, Alain

Case Sensitive Counting & Filtering
 

Instead of this construction...

ISNUMBER(FIND("aZ",RIGHT(Field1,2)))*(

I would have used this...

EXACT("aZ",RIGHT(Field1,2))

What do you mean by "I have the impression that Excel becomes very slow by
using this"? Just how many of these "counts" are you doing? Yes, if you are
doing a lot of them, the array nature of the SUMPRODUCT would affect
performance, but if you are only using one (or, perhaps, only a handful) of
them, you shouldn't notice a performance hit at all.

--
Rick (MVP - Excel)


"Alain" wrote in message
...
I have a huge database in wich some fields are strings. I need to count
records based - amongst others - on the two last characters of a field.
However, I must differentiate between az, aZ, AZ etc.

The COUNTIF does not allow for that (Right?).
I can circumvent the COUNTIF through using SUMPRODUCT, e.g.

SUMPRODUCT((ISNUMBER(FIND("aZ",RIGHT(Field1,2)))*( Field2=condition)))

I have the impression that Excel becomes very slow by using this.

So far for the COUNT; but I would liek as well to filter specific records
on
the database with e.g. the Advanced Filter, how do I enter a criterion to
filter out records with last 2 characters "aZ" (and not extract "AZ" and
so
on)?

Thanks, Alain