CODEWITHSUNDEEP
excelexcel-formula
SeanC
SeanC

Reputation: 15923

SUMIF(S) with VLOOKUP

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

Upvotes: 3

Views: 16500

Answers (1)

barry houdini
barry houdini

Reputation: 46341

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

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

confirmed with CTRL+SHIFT+ENTER

where Regions and Salesmen represent the two columns of your table

Edit:

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,Salesmen),$B$3),Sheet1!$G:$G,TEXT($E18,"yyyy-mm")))

still using CTRL+SHIFT+ENTER

Upvotes: 2

Related Questions