# 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

#1
September 17, 2009 at 09:02:18
 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 arguments1. The range to be searched2. The word to be searched forA third, optional argument can be included - it allows for words separated by something other than a space, e.g. banana:banana:bananaThe 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/AI 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

Report •

#2
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 •

#3
September 18, 2009 at 06:45:48
 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 D10Example 1: if C10 contains Apples Oranges Orangesand 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

Report •

Related Solutions

#4
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 •

#5
September 18, 2009 at 10:51:45
 Hi,Without changing the xtndCount function, you can use standard Excel formulas to get what you want.As suggested, Column A is mealsColumn B is drinks (with each meal)Then for columns C, D E etc. put the drink name in row 1Make sure spelling and capitalization are the same as in column BIn cell C2 enter the following formula:=xtndcount(\$B2,C\$1)Note the \$ signsNow drag the formula to the right under each drink nameThen 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 D5This 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 namesThe 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.RegardsPS mine's a pizza and cola!

Report •

#6
September 21, 2009 at 01:38:12

Report •

#7
September 21, 2009 at 04:13:13