Friday, April 12, 2013

yogi_Count Instances Of Text String Within Multiple Cells In Another Sheet


                                          Google Spreadsheet   Post  #1114
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Apr 12, 2013
user Ian Burchette :(http://productforums.google.com/forum/?zx=k43irhucp48p#!category-topic/docs/spreadsheets/GPEE6ZEuF5E)
Function: COUNT - text string within multiple cells

Hi!

I have a rather complex sheet setup and I'm trying to use a counting function to ascertain how many instances of a text string appear. I'll simplify it here as best I can.

I found this example which seems to go someway close to answering but I can't seem to adapt it to suit my purpose. I suspect part of the issue is my understanding of the filter and/or search string.

So here's my scenario..

I have two sheets within a doc (SHEET1 and SHEET2). On SHEET1 are two columns:
Column A -  a list of numbers running 1.1, 1.2, ...    These are external references and so carry on to 1.10, 1.11 and go on to 2.1, 2.2 etc. so are not technically "numerically" ordered - I'll call these strings now.
Column B - a count of how many times the string in column A is used in a column in SHEET2.
SHEET2 is a collaborative sheet whereby a number of people enter these references into a single cell in Column A

Obviously, if each cell only had one entry, a simple COUNTIF would suffice:
=COUNTIF(SHEET2!$A$1:$A$50,A1) and drag that down as needed.
but the issue is that sometimes it might contain a single reference (eg 4.1), but other times it might have numerous references (eg 4.1, 4.4, 4.6, 4.7)

So, for example)
SHEET2 looks like:
  |      A        | 
1|   3.2         |
2|   3.2         |
3|   3.2, 3.4  |
4|   3.5         |

In SHEET1, I need it to show:
  |      A        |        B        |
...
       3.1                0
       3.2                3
       3.3                0
       3.4                1
       3.5                1

Hope that makes sense...? Is this possible? I failed miserably at using wildcards, but the example I found seems to be very similar so I'm not giving up hope and I simply hoping it was my execution of the commend that failed!

Thanks in advance for any advice offered. 
----------------------------------------------------------------------------------------------------------
following is a solution to the problem


No comments:

Post a Comment