Monday, April 15, 2013

yogi_Compute Instances Of Words (specified in Sheet2) That Occur In Phrases In Column D Of Sheet1


                                          Google Spreadsheet   Post  #1132
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Apr 15, 2013
user Hassan Shahin :(http://productforums.google.com/forum/?zx=9y7wjsaj5nsz#!mydiscussions/docs/L1bitDAG_50)
Using Query with a condition that is in another sheet
Hi,

How can I use the query function where the "where" part of the query should point to a column in another sheet. For example, Say I have Sheet1 and Sheet2. Sheet1 has columns, A, B, C. I want to have column A in Sheet2 have some values, and in column B of Sheet2, I want to populate, the sum or count of Column A of Sheet1 where the value contains what is column A of Sheet2. Many thanks and sorry if this might been answered before.

Hassan
---
--------------------------------------------------------------------------------------------
following is a solution to the problem


1 comment:

  1. Hi Yogi,

    Thanks. Definitely, it works. However, may I suggest amending it to something like:

    =IF(NOT(isBlank(A2)), ArrayFormula(count(unique(iferror(transpose(iferror(search(A2,Sheet1!D:D,transpose(row(A$1:$254))))))))), "")

    so that if I have a blank cell in the A column, I don't get a positive count.

    Also, would it be possible to use a reg expression to count the verb that comes after the phrase: "to be able to", rather than counting every occurrence of the 'Verb" in the row.

    Finally, I want to thank you for your great assistance. Hassan

    ReplyDelete