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
---
Many thanks Yogi.
I am sharing the sheet on this link:
https://docs.google.com/
I am sharing the sheet on this link:
https://docs.google.com/
--------------------------------------------------------------------------------------------
following is a solution to the problem
Hi Yogi,
ReplyDeleteThanks. 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