title: Excel: Aggregate function combined with IF function
body: <p>I have an Excel formula I'm having trouble with. I have searched for an answer on Google and YouTube but can't seem to find one.</p>
  
  <p>In Excel I'm using an aggregate function with two criteria. Based on "Location" and "Function" I'm able to extract records from endless rows and columns of data. </p>
  
  <p>I have a dropdown list for both Location and Function. For "Location" I want to be able to choose between: </p>
  
  <blockquote>
    <p>No Selection, Amsterdam, Paris, London and Madrid. </p>
  </blockquote>
  
  <p>For "Function" I want to be able to choose between: </p>
  
  <blockquote>
    <p>No Selection, Banker, Banker II and Assistant. </p>
  </blockquote>
  
  <p>I'm using the following formula:</p>
  
  <pre><code>=IF(ROWS(M$2:M2)&gt;$I$6;"";INDEX(source[Name];AGGREGATE(15;6;(ROW(source[Name])-ROW(source[#Headers]))/((source[Location]=$I$2)*(source[Function]=$I$3));ROWS(M$2:M2))))
  </code></pre>
  
  <p>I want to be able to extract data based on two criteria but also based on one criteria. That's why I'm using "No Selection". No whenever I choose "No Selection" for Location, I want to be able to extract the data for all the bankers in all of the locations. </p>
  
  <p>The problem I'm having is with the above formula I have to choose two criteria. Whenever I select "No Selection" on one of the dropdown lists it wont extract data. </p>
  
  <p>I know an IF function can be used but I can't get it right. It's a school project I'm working on and my knowledge about Excel is very limited. </p>
  
  <p>Link to an example I'm working with: <a href="https://www.dropbox.com/s/mhsfzv7d63y7rb7/example%281%29.xlsx?dl=0" rel="nofollow noreferrer">https://www.dropbox.com/s/mhsfzv7d63y7rb7/example%281%29.xlsx?dl=0</a></p>