Count Case Sensitive text

[BradleyS]

I have the following formula: =SUMPRODUCT(--(COUNTIFS(DATAID,A2,CODE,"745D4*")))
This works OK, but the CODE needs to be case sensitive, so that it doesn't count 745d4

The criteria is a list of codes and ids on another sheet. There are hundreds of different codes
DATAID is the DNR for the IDs, and CODE is the DNR for the Codes

ID	Code
1	745d400
1	745D400
1	745D411
2	42K..00

On another sheet I need to count up the codes, for each ID

id	Count of 745D4	Count of 42K
1	2	0
2	0	1

I also need to include multiple codes in a count which I use: =SUMPRODUCT(--(COUNTIFS(DATAID,A2,CODE,{"745D4*","22a*"})))
However, this also needs to be case sensative, so that 22A is not counted.

How do I set the formulas so that they only count the correct case text?

 

[Joe4]

This link will show you how to do Case Sensitive Counts.

Count cells equal to case sensitive

To count cells that contain specific text, taking into account upper and lower case, you can use a formula based on the EXACT function together with the SUMPRODUCT function. In the example shown, E5 contains this formula, copied down: =SUMPRODUCT(--EXACT(D5,data)) Where data is the named range...

exceljet.net

 

[BradleyS]

This link will show you how to do Case Sensitive Counts.

Count cells equal to case sensitive

To count cells that contain specific text, taking into account upper and lower case, you can use a formula based on the EXACT function together with the SUMPRODUCT function. In the example shown, E5 contains this formula, copied down: =SUMPRODUCT(--EXACT(D5,data)) Where data is the named range...

exceljet.net

I've seen that and tried it, but EXACT means that I can't use and asterisk wildcard, plus I couldn't combine it into a formula so that it only counted the items against each ID.

 

[Joe4]

I've seen that and tried it, but EXACT means that I can't use and asterisk wildcard, plus I couldn't combine it into a formula so that it only counted the items against each ID.

Yeah, you are trying to incorporate two opposing things, here:
- Case Sensitive, which means EXACT
- Wildcards, which means approximate

Trying to do both at the same time is tricky, because they are polar opposites, as far as logic goes.

The only thing I can think of is to incorporate the use of a helper column to pull off the piece you want to match, i.e. the first 5 digits in that one example ("745D4"). Then you could use that in your COUNTIFS without the wildcard. But if it is not always the first 5 digits (or some standard like that), I am not sure how you would accomplish it.

 

[Scott Huish]

How about:
=SUMPRODUCT(--(DATAID=1),(EXACT(LEFT(CODE,4),"745D")+(EXACT(LEFT(CODE,3),"22a"))))

 

[BradleyS]

How about:
=SUMPRODUCT(--(DATAID=1),(EXACT(LEFT(CODE,4),"745D")+(EXACT(LEFT(CODE,3),"22a"))))

This does work. Thank you.
I was trying to use FIND which is case sensitive, but was unable to find a way to use it.

 

[BradleyS]

How about:
=SUMPRODUCT(--(DATAID=1),(EXACT(LEFT(CODE,4),"745D")+(EXACT(LEFT(CODE,3),"22a"))))

This doesn't seem to be able to produce a count when using a single code search
i.e. =SUMPRODUCT(--(DATAID=A2),EXACT(LEFT(CODE,4),"745D"))

What am I missing?

 

[RoryA]

Use:

=SUMPRODUCT(--(DATAID=A2),--(EXACT(LEFT(CODE,4),"745D")))

or:

=SUMPRODUCT((DATAID=A2)*(EXACT(LEFT(CODE,4),"745D")))

In your multi-criteria one, the + was taking care of coercing the True/False values to 1/0 respectively. Either using -- or * will achieve the same thing.

 

[BradleyS]

Use:

=SUMPRODUCT(--(DATAID=A2),--(EXACT(LEFT(CODE,4),"745D")))

or:

=SUMPRODUCT((DATAID=A2)*(EXACT(LEFT(CODE,4),"745D")))

In your multi-criteria one, the + was taking care of coercing the True/False values to 1/0 respectively. Either using -- or * will achieve the same thing.

Thank you