I have a database where there are multiple values entered in each cell. Is it possible to search for the sum of hits for a given value instead of the sum of cells that match my criteria? For example, if my database was A1:d1. A2= apple, B2=banana, C2=banana banana, and D2= banana. I would want to find 4 bananas. The DcountA function only finds 3.

Hi, Here is a custom function that will count more than one occurrence of a word in the cells in the specified range. As this is a function you use it as a formula: =xtndcount(C7:C12,"banana") The formula takes two mandatory arguments 1. The range to be searched 2. The word to be searched for A third, optional argument can be included - it allows for words separated by something other than a space, e.g. banana:banana:banana The formula for this would be: =xtndcount(C7:C12,"banana",":") Enter the following in a standard VBA module: Public Function xtndCount _ (rngRange As Range, strFind As String, Optional strSep As String) As Double ' a function to count text in cells in a range including more ' than one occurrence of the text in a cell. An optional ' separator can be given so that words separated by something ' other than a space can be recognized ' Dim rngCell As Range Dim strContents As String Dim intSepLen As Integer Dim dblCount As Double Dim intArray(255) As Integer Dim m As Integer Dim n As Integer Dim o As Integer On Error GoTo ErrHand 'set the separator between words to a space unless a different 'separator was passed to the function If strSep = "" Then strSep = " " End If ' get length of separator intSepLen = Len(strSep) For Each rngCell In rngRange.Cells strContents = Trim(rngCell.Value) n = InStr(1, strContents, strSep) If n > 0 Then 'at least one separator, so more than one word 'put location of separators in an array o = 1 intArray(0) = 1 For m = 1 To Len(strContents) If Mid(strContents, m, intSepLen) = strSep Then intArray(o) = m + 1 o = o + 1 End If Next m intArray(o) = Len(strContents) 'test each word in cell For m = 1 To o If InStr(1, Mid(strContents, intArray(m - 1), intArray(m) - intArray(m - 1) + 1), strFind) > 0 Then dblCount = dblCount + 1 o = o + 1 End If Next m Else 'only one word in cell If InStr(1, strContents, strFind) > 0 Then dblCount = dblCount + 1 End If End If Next 'return number of matches found xtndCount = dblCount Exit Function ' 'error handler ErrHand: xtndCount = CVErr(xlErrNA) End Function Note that the text from Public Function to As Double is all on one line. This function only has rudimentary error handling. An error will return #N/A I have only tested this quickly on a limited amount of data, so there will be circumstances where it doesn't work. It probably only works on single ranges, not on multiple selections. If it fails, please let me know - and I might be able to improve on it. Regards

Thank you. The function did work perfectly, however, I also need to use multiple ranges, for example count how many times an entry in Column C co-occurs with another entry in column D. Is this also possible somehow with this function?

Hi, I need some more information. Could you give an example of what output you need with some sample data. Do you need to know if the entries in C10 equal the entries in D10 Example 1: if C10 contains Apples Oranges Oranges and D10 contains Apples Apples Oranges is that a 'not equal' Example 2: if C10 contains Apples Oranges Oranges and D10 contains Pears Oranges Oranges is that an 'is equal' (if the formula is given "Oranges" as the word to count. Regards

I am not really interested in whether they are equal or not. I need to know how often a word in one cell in one column co-occurs with a different word in a different column/cell. Here's an example: In a database: Column A = meal, Column B= drinks. A2= pizza, B2 = cola lemonade cola A3 = pizza, B3= cola cola cola, A4=spaghetti, B4= cola cola cola cola cola cola cola I would then do a search for how many colas were ordered with pizza, thus, I would want it to find 5 instances. And, a count of how many colas were ordered with spaghetti: 7 times; and how many times pizza was ordered with lemonade: 1time. Many many thanks for your help.

Hi, Without changing the xtndCount function, you can use standard Excel formulas to get what you want. As suggested, Column A is meals Column B is drinks (with each meal) Then for columns C, D E etc. put the drink name in row 1 Make sure spelling and capitalization are the same as in column B In cell C2 enter the following formula: =xtndcount($B2,C$1) Note the $ signs Now drag the formula to the right under each drink name Then select the formulas in row 1 under the individual drink names and drag the formulas down for as many meals as you need. In this example only 4 meals and two drinks, so the last formula was =xtndcount($B5,D$1) in cell D5 This will give the number of each drink type for each individual meal. Here is the raw data I used: A B Meal Drinks pizza cola lemonade pizza cola cola cola spaghetti lemonade lemonade cola spaghetti cola cola cola cola cola cola cola Create a new results table. The table has the drink names in row 1 and the meal names in the first column (I was using column F) Here is what it looks like: F G H 1 cola lemonade 2 pizza 4 1 3 spaghetti 8 2 The formula in the first cell under cola is =SUMIF($A$2:$A$5,$F2,$C$2:$C$5) and under lemonade is =SUMIF($A$2:$A$5,$F2,$D$2:$D$5) Drag both of these formulas down for as many rows as you have meal names The SUMIF function is a conditional sum only adding values together when there is a match. See the help information for more detail on how it works. Regards PS mine's a pizza and cola!

Your wonderful. Thank you!