Friday, June 6, 2014

Yogi_Count Instances of Unique Entry (or entries) In A List


                                         Google Spreadsheet   Post  #1663
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-06-2014
post by Dan Kinchen: (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/H9heNIyKYoA)
Count the number of unique string occurrences in a range, keep in mind words within words
I have a sheet named Log with table range of data. In one column I have a list of sprint task types. I have given this the range name of SprintType. My problem is reporting this range.

I have working code, BUT one of my returning counts in my formula is wrong because a word "planned" is contained inside of other words "unplanned".

I only have 3 values.

 - backlog
 - planned
 - unplanned

Table Data Example:

Sprint Type

backlog
backlog
backlog
planned
planned
planned
unplanned

The count should be backlog (30), planned (3), and unplanned (1). Instead I have 4 for planned and 1 for unplanned.

Here is the formula.

=COUNTA(IFERROR(FILTER(Log!SprintType;SEARCH("planned";Log!SprintType))))

thanks!
---
oh and no Queries please. I can solve this with a query but due to my advance reports it is not a good idea.
-------------------------------------------------------------------------------------------------------------------------------------------
I don't understand why use of QUERY is not a good idea ... in any event have a look at the solution to a bit more generalized problem