Hi folks,
I am trying to figure out how to return the count of individual delimited values in multiple cells. So for example:
In my worksheet, (not attached), I have cell BO21, it contains the following "WL2, CU1".
I use this formula to count the number of individual delimited entries in that cell:
=LEN(TRIM(BO21))-LEN(SUBSTITUTE(TRIM(BO21),",",""))+1
This works. It returns "2".
However I want the formula to return this count across multiple discontinuous cells, i.e. cell BO21 as above and cell BO26, BO31 etc. Giving me the total count of delimited values from all of these cells in one return.
I guess I could do each cell individually with the formula above and then consolidate those individual returns but I would much rather have one formula that does this if at all possible.
Can anyone help?
Alan
Hi Alan,
Try this:
=SUMPRODUCT(--ISNUMBER(SEARCH(", ",$BO$2:$BO$21)))*2
Adjust the cell range to suit.
Mynda
Hi Alan
I would do the following:
1) Concatenate/join the cells together (using TEXTJOIN or CONCATENATE)
2) Substitute the delimiters with blank
3) Compare the before and after length +1
Something like this, using the TEXTJOIN function:
=LEN(TRIM(TEXTJOIN(",",TRUE,BO21,BO26,BO31)))-LEN(TRIM(SUBSTITUTE(TEXTJOIN(",",TRUE,BO21,BO26,BO31),",","")))+1
Need to watch out for empty cells if you are using CONCATENATE.
Hope this helps.
Sunny
Hi Mynda/Sunny, thank you both for the help!
Both the solutions appear to work on first try, I'll be building the sheet out a little later to will see how I get on.
Mynda for the SUMPRODUCT formula can I use that for non-continuous cell references? The range approach works but the data I am trying to evaluate is not in a continuous range and when I try to use the formula for individual cell references I am getting an error.
Thanks to you both again!
Alan
Hi Alan,
The SEARCH function doesn't take non-contiguous ranges. If the other cells in column BO don't contain a comma followed by a space i.e. the text being searched for, then they won't be counted anyway so you could just insert a contiguous range.
You could write separate formulas for the non-contiguous ranges and add them together, but if you've got a lot of ranges then that's going to get messy, in which case Sunny's formula might suit you better.
Mynda
Hi Mynda,
Thanks for the explanation. Your comment made me realize that I could search for any text not just the delimiter I was using. All the unique entries I have are prefixed with "WL" so I substituted that for the "," in your formula and it works a treat! Sunny, your is working too so thank you again.
Side bar question - I wonder if there is any way to tell which formulas work with non-continuous ranges and which do not? Or do you simply have to learn this through trial and error? (or reading about them of course).
Cheers,
Alan
Glad it’s working!
Good question about which functions can handle non-contiguous ranges. It’ll be functions that take multiple ranges e.g. SUM, AVERAGE etc. but I’m not aware of a definitive list.
