Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Database Functions - Strange results
Use a wildcard in your criteria.
Try *Red HTH, -- Gary Brown 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Database Functions - Strange results
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" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Database Functions - Strange results
Gary,
That did the trick! Thanks!!! Bob "Gary L Brown" wrote: Use a wildcard in your criteria. Try *Red HTH, -- Gary Brown 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup on pivot table results = #N/A | Excel Worksheet Functions | |||
excel database statistical functions (DSUM etc..) | Excel Discussion (Misc queries) | |||
functions are not displaying the results | Excel Worksheet Functions | |||
Graphing Database Growth Rate | Charts and Charting in Excel | |||
PivotTable canned functions | Excel Discussion (Misc queries) |