SUMIF(S)与VLOOKUP [英] SUMIF(S) with VLOOKUP

问题描述

我正在尝试将VLOOKUP函数与sumif函数一起使用.

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.

例如:

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)

但是我想在区域上看到一个sumif.数据来自其他地方，并且长度可变，因此很难创建一个辅助列

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

我一直在思考类似于vlookup的事情，但是我无法使其正常工作

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")))

推荐答案

您可以对"RegionA"使用类似的数组公式:

You can use an array formula like this for "RegionA":

= SUM(SUMIFS(Data，$ C:$ C，DateTest，$ D:$ D，IF(Regions ="RegionA"，Salesmen)))

已通过 CTRL + SHIFT + ENTER

其中Regions和Salesmen代表表的两列

where Regions and Salesmen represent the two columns of your table

在您规定要使用推销员或地区的情况下，请尝试这样

With your stipulation to use either Salesman or Region try like this

= SUM(SUMIFS(Sheet1！$ H:$ H，Sheet1！$ J:$ J，IF(LEFT($ B $ 3,3)="Reg"，IF(Regions = $ B $ 3，业务员)，$ B $ 3)，Sheet1！$ G:$ G，TEXT($ E18，"yyyy-mm")))

仍使用 CTRL + SHIFT + ENTER

这篇关于SUMIF(S)与VLOOKUP的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！