title: Excel: Sum a range of cells if a related key is in a range of values using a formula
body: <p>I'm trying to return the sum of a range of cells if an associated value is in a range of values used as filtering conditions. For example, using the following data table:</p>
  
  <pre><code>  Keys      Values
     A        500
     B        300
     C        600
     D         20
     A        150
     C        600
  </code></pre>
  
  <p>and the following list of keys to be summed:</p>
  
  <pre><code>Sum Keys:
     A
     D
  </code></pre>
  
  <p>I'm looking for a formulaic way to return 500+20+150 = 670. </p>
  
  <p>I know you can use multiple SUMIFs, but the number of keys will change based on what the user wants to filter on, and writing enough SUMIFs to cover the max realistic cases (like 10ish) seems excessive, and I feel like there must be an easier way.</p>
  
  <p>In my head, the formula would look something like this:</p>
  
  <pre><code>=SUMIFS(Values, Keys, OR(Sum Keys), ...(optional other conditions))
  </code></pre>
  
  <p>I realize that this is an easy VBA problem, but I'm trying to avoid that because the spreadsheet will be audited by people with varying (or no) VBA knowledge. Also, adding an indicator column to the data to show if the key is in the <code>Sum Keys</code> range isn't realistic because there will be multiple tabs summarizing the data, each with their own filters on <code>Key</code></p>
  
  <p>Any help is appreciated, though I realize this may be impossible without VBA. Please let me know if more information is needed.</p>
  
  <p>Thanks!</p>
  
  <p>EDIT: Thanks barry houdini for the answer! See the comments of his post for the general solution for summing based on multiple ranges of acceptable criteria.</p>