How to use COUNTIFS with case sensitive criteria

Question

I'm trying to use COUNTIFS function where the criteria is upper and lower case characters. However the COUNTIFS result counts both upper and lower characters. That is two "b" instead of one "b" and one "B".

Answer 1

For your C2 cell, use this instead. COUNTIF and COUNTIFS are not case-sensitive.

=SUMPRODUCT(--EXACT(Dtype,C$1))

OR

=SUMPRODUCT(--EXACT($A:$A,C$1))

Answer 2

Use this formula in cell C2:

=BYCOL(C1:G1, LAMBDA(a, SUM(--EXACT(A2:A6, a))))

or this formula and drag it right:

=SUM(--EXACT($A$2:$A$6, C1))

HTH

IlirU

Answer 3

Hi,

In cell C2, enter this formula

=BYCOL(C1:G1,LAMBDA(c,SUM(1*(REGEXTEST($A$2:$A$6,c)))))

Hope this helps.

Answer 4

Still investigating.