Database Functions - Strange results

Cell A1 = "Red"
Cell A2 = "Green, Red"

When I use DCOUNTA to determine the number of "Red" occurrences, the result
is 1 (rather than 2). Can someone explain why this happens (and a solution
for obtaining the correct count)? Thanks for the help.

Use a wildcard in your criteria.
Try *Red
HTH,
--
Gary Brown
gary_brown@ge_NOSPAM.com
If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"Bob" wrote:

> Cell A1 = "Red"
> Cell A2 = "Green, Red"
>
> When I use DCOUNTA to determine the number of "Red" occurrences, the result
> is 1 (rather than 2). Can someone explain why this happens (and a solution
> for obtaining the correct count)? Thanks for the help.

Bob
That happens because the DCOUNTA function counts the number of cells
that contain "Red" as the whole entry in the cell. That's 1.
I don't know if there is a formula way to do what you want, but you can
do it with the following macro. I wrote the macro assuming that your data
is in Column A starting with A1. Also, I assumed you wanted to count all
instances of "Red" regardless of case. Note that all instances of "red"
will be counted even if the "red" is not a separate word. For instance,
"redress" is one instance of "red". Post back if this does not do what you
want. HTH Otto
Sub CountRed()
Dim RngColA As Range
Dim i As Range
Dim c As Long
c = 0
Set RngColA = Range("A1", Range("A" & Rows.Count).End(xlUp))
For Each i In RngColA
If InStr(UCase(i.Value), "RED") > 0 Then c = c + 1
Next i
MsgBox c
End Sub
"Bob" <[email protected]> wrote in message
news:[email protected]...
> Cell A1 = "Red"
> Cell A2 = "Green, Red"
>
> When I use DCOUNTA to determine the number of "Red" occurrences, the
> result
> is 1 (rather than 2). Can someone explain why this happens (and a
> solution
> for obtaining the correct count)? Thanks for the help.

Gary,
That did the trick! Thanks!!!
Bob

"Gary L Brown" wrote:

> Use a wildcard in your criteria.
> Try *Red
> HTH,
> --
> Gary Brown
> gary_brown@ge_NOSPAM.com
> If this post was helpful, please click the ''Yes'' button next to ''Was this
> Post Helpfull to you?''.
>
>
> "Bob" wrote:
>
> > Cell A1 = "Red"
> > Cell A2 = "Green, Red"
> >
> > When I use DCOUNTA to determine the number of "Red" occurrences, the result
> > is 1 (rather than 2). Can someone explain why this happens (and a solution
> > for obtaining the correct count)? Thanks for the help.