title: Excel complex countifs with embedded if's
body: <p>I want to create a dynamic countifs based on some form controls check boxes.
  On columns in which there are no blanks, I do not need to do anything special however if a blank can be present in a column, this becomes trickier and I have to use a sum(countifs()) array formula to get around this.</p>
  
  <p>to increase the difficulty even further, I need to add multiple checkboxes to filter or unfilter values.</p>
  
  <pre><code>=SUM(COUNTIFS(
  Data[creation date],"&gt;=01/01/2016",                            regular filter
  Data[creation date],"&lt;01/01/2017",                             regular filter
  Data[technical],"Y",                                           regular filter
  Data[Category],IF($U$17,"&lt;&gt;T",{"*","="}),                      count all and blanks                 
  Data[Flow],IF($U$14,"Y","*"),
  Data[Status],IF($U$15,{"*","="},"&lt;&gt;Cancelled"),        not cancelled OR pending
  Data[Status],IF($U$15,{"*","="},"&lt;&gt;Pending"),
  ))
  </code></pre>
  
  <p>Although this appears like it MAY do what I intend it to do, it has some side effects that cause it to not work. Due to the way array's work when calculating the values, I think the loop mechanism works like this:</p>
  
  <pre><code>for i = 0 to 1
      filter Category[i]
      filter Status[i]
  next i
  </code></pre>
  
  <p>instead I would need it to operate like:</p>
  
  <pre><code>for i = 0 to 1
      filter Category[i]
      for j = 0 to 1
          filter Status[j]
      next j
  next I
  </code></pre>
  
  <p>So is this realistically possible?</p>