counting multiple values in one cell

Microsoft Excel 2003 (full)
September 17, 2009 at 05:55:50
Specs: Windows XP
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.

See More: counting multiple values in one cell

Report •

September 17, 2009 at 09:02:18

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:
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:

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
    'only one word in cell
        If InStr(1, strContents, strFind) > 0 Then
            dblCount = dblCount + 1
        End If
    End If

'return number of matches found
xtndCount = dblCount
Exit Function
'error handler
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.


Report •

September 18, 2009 at 03:40:14
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?

Report •

September 18, 2009 at 06:45:48

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.


Report •

Related Solutions

September 18, 2009 at 08:10:42
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.

Report •

September 18, 2009 at 10:51:45

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:
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
and under lemonade is

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.


PS mine's a pizza and cola!

Report •

September 21, 2009 at 01:38:12
Your wonderful. Thank you!

Report •

September 21, 2009 at 04:13:13
Your welcome


Report •

September 21, 2009 at 05:48:46

Since you have already started a thread related to your problem, please continue the discussion in that thread and don't repeat your question here. The same code was posted in the other thread, so it's all there for you to review.


Report •

Ask Question