title: Create a dynamic 'if' statement in Excel without VBA
body: <p><strong>* Updated *</strong></p>
  
  <p>I have a rather large excel data set that I'm trying to summarise using up to 3 dimensions: region, sector, industry.
  Any combination of these dimensions can be set or left blank and I need to create a formula that accommodates this <strong>WITHOUT</strong> using VBA.</p>
  
  <p>Within the data I've set up named ranges to refer to these dimensions.</p>
  
  <p>I'm using an array formula but I'd like to dynamically create a string which is then used as the boolean argument in the array formula. </p>
  
  <p>For instance if:</p>
  
  <pre><code>A1 = "Hong Kong" (region)
  B1 = &lt;blank&gt; (sector)
  C1 = &lt;blank&gt; (industry)
  </code></pre>
  
  <p>I create a dynamic string in D1 such that</p>
  
  <pre><code>D1 = (region="Hong Kong")
  </code></pre>
  
  <p>I then want to use the string in D1 to create an array formula</p>
  
  <pre><code>E1 = {counta(if(D1,employees))}
  </code></pre>
  
  <p>However, if the user includes a sector such that:</p>
  
  <pre><code> A2 = "Hong Kong" (region)
   B2 = "finance" (sector)
   C2 = &lt;blank&gt; (industry)
  </code></pre>
  
  <p>Then I want the string in D2 to update to:</p>
  
  <pre><code>D2 = (region="Hong Kong")*(sector="finance")
  </code></pre>
  
  <p>Which then automatically updates the value in E2 which still has the same formula.</p>
  
  <pre><code>E2 = {counta(if(D2,employees))}
  </code></pre>
  
  <p>Is this possible? Alternatively is there any other way of achieving the same outcome, keeping in mind that I need to be able to copy D1 and E1 down into different rows so that different combinations of the dimensions can be <strong>viewed simultaneously</strong>.</p>
  
  <p>Thanks.</p>
  
  <p><strong>* Updated  *</strong></p>
  
  <p>To be clear, the reason the I need the values in column D to be dynamic is so that I can create different scenarios in Row 1, Row 2, Row 3 etc. and I need the values in column E of each row to match the criteria set in columns A:C of that row.</p>