Google Sheet Function for Get Median of a Number Conditionally

Question

I am programing a Google Sheet to summarize data from another Google Sheet.

The sheet containing data is called "Sales"

In the sales sheet, each row represents a sale. It has columns for sales rep name (G column), date of sale (N column), sales class (V column), and Working Days (W column).

I'm trying to write a formula for the following logic.

Calculate Median (working days) from column W values of rows that equal ...

sales rep = "Jane Doe",

sale date is between Jan 1, 2020 and December 31, 2020,

sale classification = "1"

Here is the function I have so far...

=MEDIAN(Sales!W:W,(if(Sales!G:G,"Jane Doe",Sales!N:N,">=1/1/2020 00:00:00", Sales!N:N,"<=12/31/2020 23:59:59",Sales!V:V,"1")))

I get an error with the above code. Any idea how to do this?

Could you provide a sample sheet, with sample data, to facilitate working on this? — kirkg13
Have you tried the formula proposed below, in cell B16 of your sample sheet? — kirkg13

kirkg13 kirkg13 · Accepted Answer · 2020-07-13T23:43:25

Does this do what you want? Note you may need to adjust the range to suit your data:

=MEDIAN(QUERY(Sales!G1:W,"select W 
          where G = 'Jane Doe' and 
                V = 1 and 
                N >= date '"&TEXT(DATEVALUE("1/1/2020"),"yyyy-mm-dd")&"' and 
                N <= date '"&TEXT(DATEVALUE("12/31/2020"),"yyyy-mm-dd")&"'",1))

If you use just the following, you can check what records it is using:

=QUERY(Sales!G1:W,"select G,V,N,W 
          where G = 'Jane Doe' and 
                V = 1 and 
                N >= date '"&TEXT(DATEVALUE("1/1/2020"),"yyyy-mm-dd")&"' and 
                N <= date '"&TEXT(DATEVALUE("12/31/2020"),"yyyy-mm-dd")&"'",1)

Let us know if this helps.

Google Sheet Function for Get Median of a Number Conditionally

1 Answers