title: How to do SUMIFS text match with multiple criteria from another column?
body: <p>I wish to categorize my transactions in a way where I can alter the categories on the fly. I think it's easier explained by showing what I have.</p>
  
  <p>I have the following tables</p>
  
  <p><strong>Transactions</strong></p>
  
  <ul>
  <li>A: Date</li>
  <li>C: Name</li>
  <li>D: Amount</li>
  </ul>
  
  <p><strong>Fast Food List:</strong></p>
  
  <ul>
  <li>L: Name (partial name since going to be doing string search)</li>
  </ul>
  
  <p>I wish to sum the transaction amount based on multiple criteria, such as date and category. Here's a formula that works:</p>
  
  <pre><code>=SUMIFS(D:D,A:A,"*03/2013*",C:C,"*"&amp;L3&amp;"*")
  </code></pre>
  
  <p>There's one fundamental problem: it only supports ONE item from the Fast Food List. Is there any way I can simply do a text stringth search across the entire Fast Food names? 
  "<em>"&amp;L3&amp;"</em>" to "<em>"&amp;L:L&amp;"</em>" or something?</p>
  
  <hr>
  
  <h2>Here are some things I've tried.</h2>
  
  <p>1) Modify the SUMIFS criteria "<em>"&amp;L3&amp;"</em>" with a boolean UDF. The issue I run into here is that I can't figure out how to pass the current Row being looped by SUMIF into the function.</p>
  
  <pre><code>Public Function checkRange(Check As String, R As Range) As Boolean
  
  For Each MyCell In R
     If InStr(Check, MyCell.Value) &gt; 0 Then
          checkRange = True
      End If
  Next MyCell
  
  End Function
  </code></pre>
  
  <p>If I could send Check to this function, well I would be set.</p>
  
  <p>2) Replace the sum_range of the SUMIFS with a UDF that returns the range of rows </p>
  
  <pre><code>Public Function pruneRange(Prune_range As Range, Criteria_range As Range) As Range
  Dim Out_R As Range
  Dim Str As String
  
  ActiveWorkbook.Sheets("Vancity Trans").Activate
  ' Loop through the prune_range to make sure it belongs
  For Each Cell In Prune_range
  
      ' loop through criteria to see if it matches current Cell
      For Each MyCell In Criteria_range
          If InStr(Cell.Value, MyCell.Value) &gt; 0 Then
              ' Now append cell to Out_r and exit this foreach
                 ' Str = Str &amp; Cell.Address() &amp; ","
                 Str = Str &amp; "D" &amp; Cell.Row() &amp; ","
              Exit For
          End If
      Next MyCell
  Next Cell
  
  ' remove last comma form str
  Str = Left(Str, Len(Str) - 1)
  
  ' use str to set the range
  Set Out_R = Range(Str)
  
  ' MsgBox (Str)
  
  Set pruneRange = Out_R
  
  End Function
  </code></pre>
  
  <p>This works for a regular SUM loop, but for some reason it returns #Value when I try using it in a SUMIF or SUMIFS. Another issue is that even in the SUM loop if use C:C instead of C1:CX where X is however many rows, it crashes excel or takes forever to loop through. I'm guessing it's because excel doesn't know when to stop in a UDF unless I somehow tell it to?</p>