title: Finding the Quartile of an array with multiple criteria in Excel
body: <p>I am trying to find the 1st and 3rd quartiles from an array that is derived from the data with a set criteria.</p>
  
  <p>For example:</p>
  
  <pre><code>Apple   red     dog   10
  Apple   blue    cat   5
  Banana  blue    dog   6
  Banana  blue    cat   4
  Apple   red     dog   8
  Apple   red     dog   
  Apple   red     dog   1
  </code></pre>
  
  <p>The formula that I am using and stuck on is</p>
  
  <pre><code>=QUARTILE.INC(SMALL(IF((A1:A7="Apple")*(C1:C7="dog")*(B1:B7="red"),D1:D7),ROW(INDIRECT("1:"&amp;COUNTIFS(A1:A7,"Apple",C1:C7,"dog",B1:B7,"red")))),1)
  </code></pre>
  
  <p>The answer that is returned is 0.75 where it should really result in 4.5.
  To me it seems like excel is grabbing empty cells and including it in the derived array, is there a way to remove these empty cells from the array?</p>