Use "Mode" Function within Google Sheets Query

0
votes

I am using the Query function within a Google Sheet to pull data. I need to add criteria to only pull data based on the Mode of a specific column. The mode changes with the data so using the Mode function to make it dynamic would make life easier. This works when I hard code in the Mode value. I've tried several different variations but have not been able to get the Mode function to work when nested within my Query formula.

Here is my current formula.

=QUERY('2 - Add Raw AZ Payments Report'!A8:AA4005,"select C WHERE ((C = 'Refund') AND AA <> 0 AND B = 13742433281) OR C = 'Order' OR C = 'Adjustment' OR C = 'Chargeback Refund'",0)

13742433281 is the hardcoded Mode. Ideally, I would replace that value with the Mode function if it can be used with a Google Sheets Query.

Here is a link to a Google Sheet with the Query I am using now and the desired query with the Mode hardcoded. Essentially I would like to replace the hardcoded Mode Value with the Mode formula.

https://docs.google.com/spreadsheets/d/126IfGStccfzKLGW7dH-7TpbMbPSZXW3Of69RZ61jb8Q

Any help would be very much appreciated.

  • John
1
google-sheets-formulagoogle-sheets-api

1 Answers

1
votes

Looking at your Google Sheet, 'Query Data' tab, cell D2, you can replace 13742433282 with "&MODE('Raw Data'!B:B)&".

Also, I'm not sure if you need brackets around your OR statements because there are several OR and AND in the query. I think you may be better simplifying it like this:

=QUERY('Raw Data'!A2:D400,"select D WHERE B = "&MODE('Raw Data'!B:B)&" AND (C like 'Refund' OR C like 'Order' OR C like 'Adjustment' OR C like 'Chargeback Refund')",0)

Which basically gets rows where B is always be the mode of 'Raw Data'!B:B,

AND

the value of C is any of the following:

Refund,Order,Adjustment,Chargeback Refund

If you want the query to only find rows where there is a value in C, you could use this instead:

=QUERY('Raw Data'!A2:D400,"select D WHERE B = "&MODE('Raw Data'!B:B)&" AND C IS NOT NULL ",0)

Also, you might not need to limit the range to D400. It could be 'Raw Data'!A2:D.