Need to unique count for case sensitive values

[creative999]

Im using the formulas below to count unique values. However, just realised need to do a count based on case. Any suggestions on how i can tweak the formulas below?

=SUMPRODUCT((('SHEET1'!E3:E$5000<>"")/COUNTIF('SHEET1'!E3:E$5000,'SHEET1'!E3:E$5000&"")))
=IFERROR(SUM(--(LEN(UNIQUE(FILTER('SHEET1'!E3:E$5000, 'SHEET1'!G3:G$5000=TRUE)))>0)),0)
=IFERROR(SUM(--(LEN(UNIQUE(FILTER('SHEET1'!E3:E$5000,('SHEET1'!!H3:H$5000="AC")*('SHEET1'!!G3:G$5000=TRUE))))>0)),0)

 

[Eric W]

Hardly just a tweak! Off the top of my head, I can only think of 2 functions that are case-aware: EXACT and CODE. I tried both, and came up with this:

Book1
	E	F	G	H	I	J
1
2
3	Bob		FALSE		9	5
4	Bob		TRUE
5	Jack		TRUE
6	Jane		TRUE
7	JACK		TRUE
8	BOB		FALSE
9	Mary		FALSE
10	MarY		FALSE
11
12	Zeke		TRUE
13	Zelda		FALSE
14
15
Sheet2

Cell Formulas
Range	Formula
I3	I3	=ROWS(UNIQUE(FILTER(E3:E5000&"|"&MMULT(IFERROR(IF(CODE(MID(E3:E5000,SEQUENCE(,10),1))<90,0,1),0),2^SEQUENCE(10)),(E3:E5000<>""))))
J3	J3	=ROWS(UNIQUE(FILTER(E3:E5000&"|"&MMULT(IFERROR(IF(CODE(MID(E3:E5000,SEQUENCE(,10),1))<90,0,1),0),2^SEQUENCE(10)),(E3:E5000<>"")*(G3:G5000=TRUE))))

This should work as long as the number of characters in each word in column E is no longer than 10. If it's more than that, you can change the 2 10's in the formula to something higher, but I'd start to worry about performance. These are the first 2 of your formulas, you can probably figure out the third.

 

[jasonb75]

Not sure if this will perform any better than @Eric W's suggestions, or even if it is on the right track. It appears to work with a quick test assuming that I have broken down your original formula correctly.

=SUM(IFERROR(1/MMULT(EXACT('SHEET1'!E3:E$5000,TRANSPOSE('SHEET1'!E3:E$5000))*('SHEET1'!!H3:H$5000="AC")*('SHEET1'!G3:G$5000=TRUE),--'SHEET1'!G3:G$5000),0))

 

[jasonb75]

A slight change to my suggestion, it works as an alternative for the second formula with the AC criteria removed, but I found that there was an error in the results for the third one when there was a row with TRUE but not AC.

Expanding the suggestion to cover all 3 formulas, I think that these should be correct.

=SUM(IFERROR(1/MMULT(EXACT(A2:A10,TRANSPOSE(A2:A10))*(A2:A10<>""),--(A2:A10<>"")),0))

=SUM(IFERROR(1/MMULT(EXACT(A2:A10,TRANSPOSE(A2:A10))*(C2:C10=TRUE),--C2:C10),0))

=SUM(IFERROR(1/MMULT(EXACT(A2:A10,TRANSPOSE(A2:A10))*(B2:B10="AC")*(C2:C10=TRUE),(B2:B10="AC")*(C2:C10=TRUE)),0))