SUMIF（S）与VLOOKUP(SUMIF(S) with VLOOKUP)

 我试图使用带有sumif函数的VLOOKUP函数。 
 
 我有一个下拉框，其中包含一个名称列表，以及一系列组合。 
 
 例如： 
 
Salesman1
Salesman2
Salesman3
Salesman4
Salesman5
Salesman6
RegionA
RegionB
 
 以及一个包含销售人员和部门名单的边桌：例如： 
 
RegionA    Salesman1
RegionB    Salesman2
RegionB    Salesman3
RegionB    Salesman4
RegionA    Salesman5
RegionB    Salesman6
 
 （如果需要，可以交换此列表） 
 
 我想总结一下这些地区的推销员总数。 我可以使用个人推销员来总结 
 
=SUMIFS(Data,$C$C,DateTest,$D$D,Salesman Value)
 
 但是我希望看到有关地区的总结。 数据来自其他地方，并且长度可变，因此难以创建辅助列 
 
 我正在考虑vlookup的一些东西，但我无法让它工作 
 
=SUMIFS(Data,$C$C,DateTest,$D$D,ISNA(VLOOKUP(Salesman Value,Table!Regions,2,FALSE)))
 
 澄清：下拉列表包含销售人员和地区，我希望能够两种方式相加（为清晰起见，公式分开） 
 
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")))

I am trying to use the VLOOKUP function with a sumif function.
 
I have a drop down box that has a list of names, combined with a list of combinations.
 
e.g.: 
 
Salesman1
Salesman2
Salesman3
Salesman4
Salesman5
Salesman6
RegionA
RegionB
 
and a side table that has a list of salesmen and regoins: e.g.: 
 
RegionA    Salesman1
RegionB    Salesman2
RegionB    Salesman3
RegionB    Salesman4
RegionA    Salesman5
RegionB    Salesman6
 
(This list can be swapped if needed)
 
I would like to sum up the totals of Salesmen in the regions. I can sumif using the individual salesmen
 
=SUMIFS(Data,$C$C,DateTest,$D$D,Salesman Value)
 
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
 
I was thinking of something along the lines of vlookup, but I can't get it to work
 
=SUMIFS(Data,$C$C,DateTest,$D$D,ISNA(VLOOKUP(Salesman Value,Table!Regions,2,FALSE)))
 
clarification: The dropdown contains both salesmen and regions, and I wish to be able to sum both ways (formula split for clarity)
 
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")))

原文：https://stackoverflow.com/questions/16280438