title: SUMIF(S) with VLOOKUP
body: <p>I am trying to use the VLOOKUP function with a sumif function.</p>
  
  <p>I have a drop down box that has a list of names, combined with a list of combinations.</p>
  
  <p>e.g.:  </p>
  
  <pre>Salesman1
  Salesman2
  Salesman3
  Salesman4
  Salesman5
  Salesman6
  RegionA
  RegionB</pre>
  
  <p>and a side table that has a list of salesmen and regoins:
  e.g.:  </p>
  
  <pre>RegionA    Salesman1
  RegionB    Salesman2
  RegionB    Salesman3
  RegionB    Salesman4
  RegionA    Salesman5
  RegionB    Salesman6</pre>
  
  <p>(This list can be swapped if needed)</p>
  
  <p>I would like to sum up the totals of Salesmen in the regions. I can sumif using the individual salesmen</p>
  
  <pre><code>=SUMIFS(Data,$C$C,DateTest,$D$D,Salesman Value)
  </code></pre>
  
  <p>but I'd like to see a sumif on region. The data comes from somewhere else, and is variable in length, so a helper column would be difficult to create</p>
  
  <p>I was thinking of something along the lines of vlookup, but I can't get it to work</p>
  
  <pre><code>=SUMIFS(Data,$C$C,DateTest,$D$D,ISNA(VLOOKUP(Salesman Value,Table!Regions,2,FALSE)))
  </code></pre>
  
  <p>clarification: The dropdown contains both salesmen and regions, and I wish to be able to sum both ways (formula split for clarity)</p>
  
  <pre><code>IF(UPPER(LEFT($B$3,3))="REG",
    SUMIFS(Sheet1!$H:$H,Sheet1!$J:$J,RegionTest,Sheet1!$G:$G,TEXT($E18,"yyyy-mm")),
    SUMIFS(Sheet1!$H:$H,Sheet1!$J:$J,$B$3,Sheet1!$G:$G,TEXT($E18,"yyyy-mm")))
  </code></pre>