title: Sorting for Unique values with multiple IF formula in spreadsheet
body: <p>I have a sheet of sales entries sorted by date naturally. Each property can have many entries (cancelations are entered as negative purchases). My ordeal is that I need to consolidate duplicates and sum product columns for SIGNED marked rows only.</p>
  
  <p>Secondly, all 0 values (checked by summing all product columns, if 0 = canceled) need to not be displayed from the get go. Essentially The list must be sorted by 2 criteria, then summed accordingly to create an "Active Accounts" table that further data can be extracted from.</p>
  
  <p>Thirdly, this must all happen in real time. When sales are added, tables should update effectively. </p>
  
  <p>In an ideal world creating an auxiliary column that summed product colums if "signed" and returned yes/no if 0 because it forces me to use a view to display only active accounts, as opposed to this happening dynamically at the tables inception. If necessary, I guess I can go that route but hopefully you guys here can help me out in directing me one way or another.</p>
  
  <p>I am going to do my best to illustrate what I have done/tried so far:</p>
  
  <pre><code>Client     Product 1     Product 2     Product 3    Status
  AA            50            50            50        Signed
  BB            0             0             50        Signed
  AA            0            -50            0         Signed
  CC            50            0             0         Signed
  CC           -50            0             0         Signed
  BB            0             0            -50        Pending
  AA            0             0             50        Signed
  AA            0             0             50        Pending
  </code></pre>
  
  <p>What my second sheet will look like given the above data.</p>
  
  <pre><code> Client     Product 1     Product 2     Product 3    Status
  AA            50            0             100       Signed
  BB            0             0             50        Signed
  </code></pre>
  
  <p>At this point in time I only know of "=unique(client)" in A1 to dynamically get a unique list dynamically without a ridiculous formula. And then doing a if(sum($b2:$d2)=0,"no",yes")) after the status column that would then be sorted out with a view. My only problem is that "Pending" sales, get pulled into my table. I have tried to solve this by sorting it at the product sum column.</p>
  
  <pre><code>=if(isblank(A3),,(sumif(client,A2,product1)))
  </code></pre>
  
  <p>This works perfectly for me, but it does not sort for "Signed" "Pending"</p>
  
  <p>I have tried :</p>
  
  <pre><code>=if(isblank(Properties),,(IF(And(Properties=$A2,status="Signed"),SUM(SEO))))
  </code></pre>
  
  <p>but I get the error "An array value cannot be found."</p>
  
  <p>I'm really stumped as to what steps to take next to tackle this problem.</p>