CountIf on Upper/Lowercase

[ws]

COUNTIF (A1:A10,"=A") treats "A" and "a" the same
condition. Is there any way that I can differentiate the
them (thus uppercase and lowercase) in countif & sumif in
Excel2000 or Execl97?

Pls advise.

Thanks
WS

 

[Chip Pearson]

WS,

To get the same functionality as COUNTIF, use
=SUM(--EXACT(A1:A10,"A"))

To get the same functionality of SUMIF, use
=SUM((EXACT(A1:A10,"A")*B1:B10))

Both of these are array formulas, so you must press
Ctrl+Shift+Enter rather than just Enter when you first enter the
formula and whenever you edit it later. If you do this properly,
Excel will display the formula enclosed in curly braces {}.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Ron de Bruin]

You can use the Exact function

This is a array formula
(entered with Ctrl+Shift+Enter)

=SUM((EXACT(A1:A10,"A")+0))

 

[Harlan Grove]

To get the same functionality as COUNTIF, use
=SUM(--EXACT(A1:A10,"A"))

....

Or with a little more typing array entry is no longer necessary.

=SUMPRODUCT(--EXACT(A1:A10,"A"))

Then again, perhaps Chip knows whether Microsoft is about to deprecate using
SUMPRODUCT in this way, leaving array-entered SUM as the only way to do
this.

 

[WS]

Chip
That's great. Many thanks for your help.

One more question is whether there is any Excel function
to count on the (font|color) + (uppercase|lowercase).

Regards
WS

 

[Chip Pearson]

WS,

You can't access the font color information with any built-in
Excel function, so you need to use a function written in VBA.
See www.cpearson.com/excel/colors.htm for example code to sum or
count cells with a specific background or font color.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Peo Sjoblom]

For font colour see

http://www.cpearson.com/excel/colors.htm

when you say upper/lower case, are you meaning like

A
a
a
a
B
b
b

would count that there are 2 upper case in the above

=SUMPRODUCT(--(CODE(A2:A10&" ")>=65),--(CODE(A2:A10&" ")<=90))

would do that

 

[Alan Beban]

The posted solutions work only for determining the number of values
equal to the specified value, not for the other comparisons that COUNTIF
measures (<>, >, <, >=, <=).

Alan Beban

 

[Harlan Grove]

The posted solutions work only for determining the number of values
equal to the specified value, not for the other comparisons that COUNTIF
measures (<>, >, <, >=, <=).

First off, if EXACT handles equality, it's easily adapted to handle
inequality. While Chip's formulas didn't explicitly handle <>, all it takes
is =SUMPRODUCT(1-EXACT(X,"Y")) for case-sensitive COUNTIF(X,"<>Y"). Surely
you could have figured this out and posted this as an addition to the thread
rather than griping about what Chip didn't mention. But that's not your
style, is it?

As for ordering comparisons, in its infinite wisdom Microsoft has decided
not to provide any support for CASE-SENSITIVE text comparisons.
Nevertheless, it can be hacked. Only the first character position at which
two strings differ is needed to determine case-sensitive ordering. Using
standard text parsing kludges, defining the name Seq referring to
=ROW(INDIRECT("1:1024")) and Pad referring to =REPT(CHAR(1),COUNT(Seq)),
case-sensitive text order comparisons can be accomplished using

X<Y:
=ISNUMBER(MATCH(TRUE,CODE(MID(X&Pad,Seq,1))<CODE(MID(Y&Pad,Seq,1)),0))

X<=Y:
=ISNUMBER(MATCH(TRUE,CODE(MID(X&Pad,Seq,1))<=CODE(MID(Y&Pad,Seq,1)),0))

X>Y:
=ISNUMBER(MATCH(TRUE,CODE(MID(X&Pad,Seq,1))>CODE(MID(Y&Pad,Seq,1)),0))

X>=Y:
=ISNUMBER(MATCH(TRUE,CODE(MID(X&Pad,Seq,1))>=CODE(MID(Y&Pad,Seq,1)),0))

In an ideal world, Excel would have a worksheet equivalent to VBA's StrComp.
Then again, in an ideal world Excel wouldn't have more than 10% market
share.

Excel is far from an ideal text processing tool, but it's not completely
incapable.

 

[Alan Beban]

With A,b,c,a,B,c,A,b,c,respectively in A1:A9 and Pad and Seq defined as
called for below, each of the following returns FALSE:

=ISNUMBER(MATCH(TRUE,CODE(MID(A1:A9&Pad,Seq,1))<CODE(MID("A"&Pad,Seq,1)),0))
=ISNUMBER(MATCH(TRUE,CODE(MID(A1:A9&Pad,Seq,1))>CODE(MID("A"&Pad,Seq,1)),0))

Is that to be expected?

Alan Beban

 

[Harlan Grove]

With A,b,c,a,B,c,A,b,c,respectively in A1:A9 and Pad and Seq defined as
called for below, each of the following returns FALSE:

=ISNUMBER(MATCH(TRUE,CODE(MID(A1:A9&Pad,Seq,1))<CODE(MID("A"&Pad,Seq,1)),0) )
)

Is that to be expected?

OK, it doesn't work with multiple cell ranges or arrays directly. Need to

= on the other. So let LPad be =REPT(CHAR(1),COUNT(Seq)) and let GPad be

=REPT(CHAR(255),COUNT(Seq)).

For COUNTIF(X,"<Y"), when X is single column, multiple row use the array
formula

=SUM(--(MMULT(--(CODE(MID(X&LPad,TRANSPOSE(Seq),1))
<CODE(MID("Y"&LPad,TRANSPOSE(Seq),1))),Seq)>0))

When X is a multiple column, single row use the array formula

=SUM(--(MMULT(TRANSPOSE(Seq),--(CODE(MID(X&LPad,Seq,1))
<CODE(MID("Y"&LPad,Seq,1))))>0))

Replace the '<' between the two CODE calls with <= for COUNTIF(X,"<=Y).
Replace the '<' between the two CODE calls with > or >= *and* replace both
LPad references with GPad for COUNTIF(X,">Y") and COUNTIF(X,">=Y"),
respectively.

Unfortunately, there's no way to handle nontrivial 2D ranges or arrays using
built-in formulas. However, the formulas above could be adapted to work with
a udf that takes a variable number of arguments and combines them all into a
single 1D array. More general and perhaps more efficient would be a udf
wrapper around VBA's StrComp. On my to-do list.

 

[Alan Beban]

For what it's worth to users, if any, who might be following this
thread, my original response in it was not a "gripe" about Chip
Pearson's or anyone else's prior response; it was simply an accurate
observation that the responses did not deal with 5 of the six comparison
operators that COUNTIF is designed to accommodate. I made the
observation in an effort to stimulate some discussion about providing
case sensitive COUNTIF-type functionality, a nontrivial subject in which
I have some interest.

After some tidying up over the weekend, the freely downloadable file at
http://home.pacbell.net/beban now contains an ArrayCountIf function
that, though certainly not a model programming efficiency, does have
significant case sensitive COUNTIF-type functionality for both arrays
and worksheet ranges. The syntax is:

ArrayCountIf(range/array, sought_value, "comparison_operator",[True])

Inserting True as the optional 4th argument provides case sensitivity.

Alan Beban

 

[Harlan Grove]

. . . it was simply an accurate
observation that the responses did not deal with 5 of the six comparison
operators that COUNTIF is designed to accommodate. . . .

Accurate, perhaps, but nevertheless obtuse with respect to <>. While it's
strictly true that Chip's =SUM(--EXACT(A1:A10,"A")) handled only equality, it's
also true that adapting this for inequality should have been trivially easy for
you: =SUM(1-EXACT(A1:A10,"A")). That you chose to point out what you perceived
as the limitations of the response rather than address those limitations
constructively is characteristic.

Fine & dandy that you've updated your function library, but there's still ample
room for improvement, even in the recently updated ArrayCountIf. Given the array
{"a";"b";"C";"d";"e";"f";"G";"h";"i";"J";"k";"l"} in S1:S12, the formula

=arraycountif(S1:S12,"Z","<",TRUE)

returns 3 (the correct result), but the formula

=arraycountif((S1:S5,S7:S12),"Z","<",TRUE)

returns 1 (which misses "G" and "J", so incorrect). At least the built-in
COUNTIF function chokes on multiple area ranges and returns error values when
fed them as 1st argument. The problem in your function is (underscores for
formatting)

'Ensure that InputArray is an array or multi-cell range
If Not IsArray(InputArray) Then
__Msg = "The first argument to this function must be . . . "
__If CalledFromWorksheet Then ArrayCountIf = Msg Else MsgBox Msg, 16
__Exit Function
Else
__arr = InputArray 'Converts an input range to an array
End If

First the philosopical question: why the heck can't the first argument be a
scalar? COUNTIF(A$2:A2,SomeOtherRange) is a fairly common idiom, when filled
down the 1st arg becomes multiple cell, but it's well-defined when the 1st arg
is single cell. I realize you do this because you iterate through the resulting
array further down in your udf, but it's unnecessary on multiple levels.

Consider.

'Ensure that InputArray is an array or multi-cell range
If Not IsArray(InputArray) Then
__InputArray = Array(InputArray)
End If

Then further down where you iterate through arr, iterate through InputArray.

For Each elem In InputArray
'whatever
Next elem

and use the default Value property of Range objects. Wouldn't this be cleaner?
Wouldn't this be more consistent with COUNTIF?

But I do have to say that your ArrayCountIf is one really ugly monster. It's way
too large and way too complicated. Here's an alternative. Does more than
ArrayCountIf in fewer than 1/3 the number of lines of code. You need to learn
efficient use of subroutines.


Function acountif( _
a As Variant, _
mp As Variant, _
Optional ci As Boolean = True _
) As Variant
'-----------------------------------------------
Dim rv As Variant
Dim i As Long, j As Long, k As Long, m As Long, n As Long

If Not TypeOf a Is Range And Not IsArray(a) Then a = Array(a)

If TypeOf mp Is Range Then mp = mp.Areas(1).Value
If Not IsArray(mp) Then mp = Array(mp)

m = UBound(mp, 1) - LBound(mp, 1)

On Error Resume Next
n = -1
n = UBound(mp, 2) - LBound(mp, 2)
Err.Clear
On Error GoTo 0

If n >= 0 Then
ReDim rv(0 To m, 0 To n)

For i = 0 To m
For j = 0 To n
rv(i, j) = acountif_sub(a, mp(i + LBound(mp, 1), j + LBound(mp, 2)), ci)
Next j
Next i

Else
ReDim rv(0 To m)

For i = 0 To m
rv(i) = acountif_sub(a, mp(i + LBound(mp, 1)), ci)
Next i

End If

If n = -1 And m = 0 Then rv = rv(0) 'scalar

acountif = rv

End Function


Private Function acountif_sub( _
a As Variant, _
mp As Variant, _
ci As Boolean _
) As Long
'--------------------------------------------------------------
'There are times when even udfs should make use of subroutines.
'--------------------------------------------------------------
Dim op As String, x As Variant, mps As String, mpe As Variant, cm As Long

If TypeOf a Is Range And (ci Or IsNumeric(mp) Or IsError(mp) Or _
TypeName(mp) = "Boolean") Then
For Each x In a.Areas
acountif_sub = acountif_sub + Application.WorksheetFunction.CountIf(x, mp)
Next x

Else
cm = IIf(ci, vbTextCompare, vbBinaryCompare)

mps = CStr(mp)

If Len(mps) = 0 Then
op = ""

ElseIf Len(mps) = 1 Then
op = "="
If mps = "=" Then mps = ""

ElseIf InStr(1, " <= <> >= ", " " & Mid(mps, 1, 2) & " ") > 0 Then
op = Mid(mps, 1, 2)
mps = Mid(mps, 3)

ElseIf InStr(1, " < = > ", " " & Mid(mps, 1, 1) & " ") > 0 Then
op = Mid(mps, 1, 1)
mps = Mid(mps, 2)

Else
op = "="

End If

If InStr(1, " #REF! #DIV/0! #NULL! #VALUE! #NAME? #NUM! #N/A ", _
" " & mps & " ") > 0 Then mpe = Evaluate(mps)

For Each x In a
If IsError(mpe) Then
If op = "=" Then
acountif_sub = acountif_sub + IIf(mpe = x, 1, 0)

ElseIf op = "<>" Then
acountif_sub = acountif_sub + IIf(mpe <> x, 1, 0)

End If

ElseIf op = "=" And StrComp(CStr(x), mps, cm) = 0 Then
acountif_sub = acountif_sub + 1

ElseIf op = "<>" And StrComp(CStr(x), mps, cm) <> 0 Then
acountif_sub = acountif_sub + 1

ElseIf op = "<" And StrComp(CStr(x), mps, cm) < 0 Then
acountif_sub = acountif_sub + 1

ElseIf op = "<=" And StrComp(CStr(x), mps, cm) <= 0 Then
acountif_sub = acountif_sub + 1

ElseIf op = ">=" And StrComp(CStr(x), mps, cm) >= 0 Then
acountif_sub = acountif_sub + 1

ElseIf op = ">" And StrComp(CStr(x), mps, cm) > 0 Then
acountif_sub = acountif_sub + 1

End If

Next x

End If

End Function


This should reproduce the same results as COUNTIF if fed the same arguments. If
so, acountif is a proper generalization of COUNTIF. Anything less is kinda
pointless. One known limitation: for array 1st arguments and range 1st arguments
for case-sensitive matching, wildcards aren't supported. If you want to add
wildcard support for case-insensitive matching when the 1st arg is an array, go
for it.

 

[Alan Beban]

. . . Given the array
{"a";"b";"C";"d";"e";"f";"G";"h";"i";"J";"k";"l"} in S1:S12, the formula

=arraycountif(S1:S12,"Z","<",TRUE)

returns 3 (the correct result), but the formula

=arraycountif((S1:S5,S7:S12),"Z","<",TRUE)

returns 1 (which misses "G" and "J", so incorrect). At least the built-in
COUNTIF function chokes on multiple area ranges and returns error values when
fed them as 1st argument.

Point taken on returning error values; it will be addressed. Note that
=ArrayCountIf(MakeArray(s1:s5,s7:s12,1),"Z","<",True) returns the
correct result.

I'll be reviewing the constructive parts of the rest of your response;
thanks.

Alan Beban

 

[Alan Beban]

. . .This [acountif] should reproduce the same results as COUNTIF if
fed the same arguments. If so, acountif is a proper generalization

of > COUNTIF. Anything less is kinda pointless.

I'm not 100% sure I understand exactly what you mean by "anything less";
and to the extent I do understand it, I guess I don't agree that it's
necessarily pointless. I did not adopt the idea that ArrayCountIf should
reproduce exactly the same results as COUNTIF, although I recognize that
the programming is simpler that way and that there is some at least
superficial appeal for the consistency of such an approach. But I was
unwilling to accept the irrationality, for example, with
blank,1,2,3,blank, respectively, in A1:A5, that =COUNTIF(A1:A5,A1)
returns 0 (as does, by your design, acountif); therefore,
=ArrayCountIf(A1:A5,A1) returns 2, the number of occurrences in the
range that match what is in A1.

There are some other deliberate discrepancies, such as the treatment of

or < error values, which the sorting algorithms recite are "valued"

equally but which COUNTIF (and, a fortiori, acountif) does not "value"
equally; ArrayCountIf does.

It is not self-evident to me that the adoption of such discrepancies
necessarily reduces the functionality of the resulting Function.

Alan Beban

 

[Harlan Grove]

...
...

There are some other deliberate discrepancies, such as the treatment of
equally but which COUNTIF (and, a fortiori, acountif) does not "value"
equally; ArrayCountIf does.

It is not self-evident to me that the adoption of such discrepancies
necessarily reduces the functionality of the resulting Function.

...

Legitimate. However, fuller documentation of the function's differences from
COUNTIF would be valuable. As for any function reproducing the ordering of
Excel's sort facility, Excel's ordering is thoroughly screwed up. Why anyone
would want to duplicate it is a mystery.

Here's an example. Consider X1:X9 containing

-
--
---
z
-z
z-
zz
-zz
z-z
zz-

These are the result of sorting in ascending order the same values originally
entered in random order. Note that Excel seems to consider '-' < 'z'. Fine so
far - specifying nonalpha characters greater or less than alpha chars is a
convention, and Excel/Microsoft are free to adopt any convention they want to AS
LONG AS IT'S CONSISTENT AND SENSIBLE. However, hyphens aren't treated sensibly.
If '-' < 'z', then why aren't '-z' and '-zz' both < 'z'? Replace '-' with 'a'
and resort in ascending order and you get

a
aa
aaa
az
azz
z
za
zaz
zz
zza

Then replace 'a' with '-' but don't sort, and you get

-
--
---
-z
-zz
z
z-
z-z
zz
zz-

As you can see, Excel's sort ordering is fubar when nonalpha characters are
involved. The benefits of trying to reproduce it in a conditional counting udf
is at best questionable. Anyway, since VBA doesn't reproduce Excel's sort
ordering, why do you think you're duplicating Excel's collation sequence?

Since you seem to want to continue discusion of of ArrayCountIf, I found another
problem in it. Given S1:S8 containing 1; 2; 3; 4; #DIV/0!; 6; 7 ; 8,
COUNTIF(S1:S8,"#DIV/0!") and COUNTIF(S1:S8,#DIV/0!) both return 1, as does
ArrayCountIf(S1:S8,#DIV/0!), but ArrayCountIf(S1:S8,"#DIV/0!") returns zero.

The reason for this apparent discrepancy is that you're separating the
comparison operator from the comparison operand. ArrayCountIf is treating second
argument "#DIV/0!" as a string. Given the way ArrayCountIf works, this may be a
necessity, but it's not necessary in COUNTIF. To count cells containing the
literal text "#DIV/0!", COUNTIF(RangeRef,"#DIV/0!*") works.

So what *exactly* do you claim that ArrayCountIf does? Or is it as much of a
mystery to you as it would be to anyone else reading your VBA code?

 

[Alan Beban]

I found another problem in . . . [ArrayCountIf]. Given S1:S8 containing 1; 2; 3; 4; #DIV/0!; 6; 7 ; 8,
COUNTIF(S1:S8,"#DIV/0!") and COUNTIF(S1:S8,#DIV/0!) both return 1, as does
ArrayCountIf(S1:S8,#DIV/0!), but ArrayCountIf(S1:S8,"#DIV/0!") returns zero.

The reason for this apparent discrepancy is that you're separating the
comparison operator from the comparison operand. ArrayCountIf is treating second
argument "#DIV/0!" as a string. Given the way ArrayCountIf works, this may be a
necessity, but it's not necessary in COUNTIF. To count cells containing the
literal text "#DIV/0!", COUNTIF(RangeRef,"#DIV/0!*") works.

In COUNTIF(S1:S8,"#DIV/0!") and COUNTIF(S1:S8, #DIV/0!), COUNTIF treats
both second arguments as the error value and, as you point out, requires
the asterisk within the quotes to distinguish between them. As one
result, with 1 in A1 and the text string (not the error value) #DIV/0!
in A2, =COUNTIF(A1:A2,A2) erroneously (I submit) returns 0. ArrayCountIf
treats "#DIV/0!" as the string text value and counts (or in other
contexts compares) the number of those, and it treats #DIV/0! as the
error value and counts (or in other contexts compares) the number of
those. In the above example, =ArrayCountIf(A1:A2,A2) returns 1, the
reasonably expected result.

Alan Beban

 

[Harlan Grove]

I found another problem in . . . [ArrayCountIf]. Given S1:S8 containing
1; 2; 3; 4; #DIV/0!; 6; 7 ; 8, COUNTIF(S1:S8,"#DIV/0!") and
COUNTIF(S1:S8,#DIV/0!) both return 1, as does ArrayCountIf(S1:S8,#DIV/0!),
but ArrayCountIf(S1:S8,"#DIV/0!") returns zero.

The reason for this apparent discrepancy is that you're separating the
comparison operator from the comparison operand. ArrayCountIf is treating
second argument "#DIV/0!" as a string. . . .

In COUNTIF(S1:S8,"#DIV/0!") and COUNTIF(S1:S8, #DIV/0!), COUNTIF treats
both second arguments as the error value and, as you point out, requires
the asterisk within the quotes to distinguish between them. As one
result, with 1 in A1 and the text string (not the error value) #DIV/0!
in A2, =COUNTIF(A1:A2,A2) erroneously (I submit) returns 0. ArrayCountIf
treats "#DIV/0!" as the string text value and counts (or in other
contexts compares) the number of those, and it treats #DIV/0! as the
error value and counts (or in other contexts compares) the number of
those. In the above example, =ArrayCountIf(A1:A2,A2) returns 1, the
reasonably expected result.

Fine. Your ArrayCountIf provides literal matching all the time, and its second
argument should be considered a literal value rather than a comparison
expression. Pity you violate that when it comes to counting 'empty blanks'.

Since you believe it's a good idea for ArrayCountIf to return different results
than COUNTIF when fed a single area range as first argument and all optional
arguments omitted, don't you believe you should point that out in the function's
description on the first page of your workbook?

 

[Dave Peterson]

Just a comment about sorting fields with dashes:
Excel just ignores the dash when it sorts.

 

[Alan Beban]

...

I found another problem in . . . [ArrayCountIf]. Given S1:S8 containing
1; 2; 3; 4; #DIV/0!; 6; 7 ; 8, COUNTIF(S1:S8,"#DIV/0!") and
COUNTIF(S1:S8,#DIV/0!) both return 1, as does ArrayCountIf(S1:S8,#DIV/0!),
but ArrayCountIf(S1:S8,"#DIV/0!") returns zero.

The reason for this apparent discrepancy is that you're separating the
comparison operator from the comparison operand. ArrayCountIf is treating
second argument "#DIV/0!" as a string. . . .

In COUNTIF(S1:S8,"#DIV/0!") and COUNTIF(S1:S8, #DIV/0!), COUNTIF treats
both second arguments as the error value and, as you point out, requires
the asterisk within the quotes to distinguish between them. As one
result, with 1 in A1 and the text string (not the error value) #DIV/0!
in A2, =COUNTIF(A1:A2,A2) erroneously (I submit) returns 0. ArrayCountIf
treats "#DIV/0!" as the string text value and counts (or in other
contexts compares) the number of those, and it treats #DIV/0! as the
error value and counts (or in other contexts compares) the number of
those. In the above example, =ArrayCountIf(A1:A2,A2) returns 1, the
reasonably expected result.

Fine. Your ArrayCountIf provides literal matching all the time, and its second
argument should be considered a literal value rather than a comparison
expression. Pity you violate that when it comes to counting 'empty blanks'.

More's the pity that Excel has no expression for a null value, which is
the source of the problem. I consider the "violation" a nit; not only
is it pointed out in the description of the function, the syntax for
ArrayCountIf is the same as the COUNTIF syntax for counting empty
blanks, and shouldn't therefore send users astray.

Since you believe it's a good idea for ArrayCountIf to return different results
than COUNTIF when fed a single area range as first argument and all optional
arguments omitted, don't you believe you should point that out in the function's
description on the first page of your workbook?

Point taken; it will be addressed. Thanks. And calling it "a good idea"
is probably a bit of an overstatement; I consider it more like the
lesser of two evils--one evil being the perpetuation of what I view as
COUNTIF's inefficiencies, irrationalities or errors, the other being the
deviation from a precise mirroring of COUNTIF's results.

Alan Beban