Search code examples
google-sheetsgoogle-sheets-formulagoogle-sheets-query

Importrange and Query Issue

What started as a pretty simple project has now gotten a bit over my head and I could really use a point in the right direction. I have two issues within this group of sheets and will include links below. I have a report being populated with two sets of data.

Problem 1: Trying to populate a cell in the "Marketing Update Sheet" with a count in "Showing Traffic- apptmanage" if two criteria are met. If column C matches a unique identifier number in cell E5 and if column A is after the date in E9.

Problem 2: I have data populating from "Marketing" sheet in B28 of the "Marketing Update Sheet" I now need to add the stipulation that the date in column 1 needs to be after the same date in cell E9.

Thank you for any guidance you can provide!

Main Sheet - "Marketing Update" https://docs.google.com/spreadsheets/d/1RIORAKgdc4EJiT4xFeQFmzs00c9de3bXKRCtF2TU1Do/edit?usp=sharing

Showing Traffic Sheet https://docs.google.com/spreadsheets/d/1Qnyn2dF8Hu7l16Cs3wRvmG8TJ8fkxm2Quqtnd9vWKCw/edit?usp=sharing

Marketing Sheet https://docs.google.com/spreadsheets/d/1MepPU0oH6Hr4VFpeFgJiYojKYAdAL1SzfLzlOGW7yDk/edit?usp=sharing

Solution

  • problem 1:

    =COUNTA(QUERY(IMPORTRANGE(
 "1Qnyn2dF8Hu7l16Cs3wRvmG8TJ8fkxm2Quqtnd9vWKCw", 
 "apptmanage!A7:C20000"),
 "SELECT Col3 WHERE Col1 > date '"&TEXT(E9,"yyyy-mm-dd")&"' 
  and (Col3 matches '"&E5&"' or Col3 matches '"&J5&"')"))

    5

    problem 2:

    =QUERY(FILTER(IMPORTRANGE("1AQUb2VcyrmpnoXztBxRMAu4z0npBEj6zPgr4BbBzuL0","advertising!A:G"), 
 INDEX(IMPORTRANGE("1AQUb2VcyrmpnoXztBxRMAu4z0npBEj6zPgr4BbBzuL0","advertising!A:G"),0,4)=E4),
 "where Col1 >= date '"&TEXT(E9, "yyyy-mm-dd")&"'")

    0