Search code examples
google-sheetsgoogle-sheets-formula

How to bring in data and aggregate from a separate Google Sheet that is also controlled by a Query Filter

I have a query filter in a main Google Sheets with some name options such as (All, Adam, Joe, Sarah)

I have another google sheet file with separate sales win/loss data and the sheet name is "Win-Loss" Which shows whether a deal was won or lost and I want to count the number of deals won, deals lost, and the deal win rate

I want the numbers to change based on the name in the dropdown in the main Google Sheet with an "all" option so something like this - (All, Adam, Joe, Sarah), the names in the deals won/lost sheet are in column B and the deal status is in column D. But the names have first and last whereas the dropdown in G1 only has first names.

I'm running into issues where all data is 0 or shows N/A. I've tried various formulas but nothing seems to be doing the trick.

Link to mock main GSheet: https://docs.google.com/spreadsheets/d/10FJYa6IsYgaoa1yf2Rd5CItEcjYmXoD1JLvlmpk5dAM/edit#gid=0

Link to mock win-loss GSheet: https://docs.google.com/spreadsheets/d/17FhkMrhjKJr1iOcJ4hpCKMwQtd6B_BAyT_XAFt7rIBc/edit#gid=0

Solution

  • Answer

    To aggregate data from the "Win/Loss" spreadsheet file into the "Main Sheet" spreadsheet file, try the following functions:

    For the Won column:

    =COUNTIF(IFNA(QUERY(IMPORTRANGE("Win/Loss_Sheet_URL", "Win-Loss!A:D"), "SELECT * WHERE Col2 CONTAINS '"&$G1&"'", 0), ""), "*"&$B4&"*")

    For the Loss column:

    =COUNTIF(IFNA(QUERY(IMPORTRANGE("Win/Loss_Sheet_URL", "Win-Loss!A:D"), "SELECT * WHERE Col2 CONTAINS '"&$G1&"'", 0), ""), "*"&$C4&"*")

    For the Win/Loss Rate column:

    =IFERROR(B5/(B5+C5), "")

    Sample Result:

    enter image description here

    These formulas use COUNTIF to tally occurrences from the "Win/Loss" sheet based on specified conditions and calculate the Win/Loss rate. Adjust the sheet URL and cell references as needed.

    References:

    These are the modified formulas for aggregating data from "Win/Loss" sheet into "Main Sheet" using Google Sheets functions: