using vlookup in the range of a sumif formula

[gbswiss]

I need to create a report that looks up data provided on another worksheet and spits out the sum of all trades maturing in less than or equal to 12 months. So the data looks like this:

Worksheet A
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Portfolio[/TD]
[TD]Maturing < 1yr[/TD]
[/TR]
[TR]
[TD]ABC123[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DEF345[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GHI678[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Worksheet B
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Portfolio[/TD]
[TD]Market Value[/TD]
[TD]Maturity[/TD]
[/TR]
[TR]
[TD]ABC123[/TD]
[TD]100[/TD]
[TD]6/15/2019[/TD]
[/TR]
[TR]
[TD]ABC123[/TD]
[TD]200[/TD]
[TD]7/31/2019[/TD]
[/TR]
[TR]
[TD]ABC123[/TD]
[TD]100[/TD]
[TD]2/15/2018[/TD]
[/TR]
[TR]
[TD]DEF345[/TD]
[TD]600[/TD]
[TD]2/28/2019[/TD]
[/TR]
[TR]
[TD]DEF345[/TD]
[TD]900[/TD]
[TD]3/15/2018[/TD]
[/TR]
[TR]
[TD]DEF345[/TD]
[TD]600[/TD]
[TD]2/1/2018 (today)[/TD]
[/TR]
[TR]
[TD]GHI678[/TD]
[TD]400[/TD]
[TD]5/31/2018[/TD]
[/TR]
[TR]
[TD]GHI678[/TD]
[TD]700[/TD]
[TD]8/1/2019[/TD]
[/TR]
[TR]
[TD]GHI678[/TD]
[TD]800[/TD]
[TD]11/30/2019[/TD]
[/TR]
[TR]
[TD]GHI678[/TD]
[TD]100[/TD]
[TD]2/1/2019
(1 yr from today)[/TD]
[/TR]
</tbody>[/TABLE]

The result should look like this:

Worksheet A
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Portfolio[/TD]
[TD]Maturing < 1yr[/TD]
[/TR]
[TR]
[TD]ABC123[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]DEF345[/TD]
[TD]1,500[/TD]
[/TR]
[TR]
[TD]GHI678[/TD]
[TD]500[/TD]
[/TR]
</tbody>[/TABLE]

I created a SumIf formula as follows, but it adds up all the maturities for all of the portfolios instead of matching it the portfolio the vlookup requires

=SUMIF(IF(A2=VLOOKUP(A2,'Worksheet B'!A2:C10,1),'Worksheet B'!C2:C10),"<=" & NOW()+365,'Worksheet B'!B2:B10)

The formula without vlookup works fine as long as I have the data all in one worksheet AND I am only referencing one row: '=SUMIF(IF(A2=A10,C10),"<=" & NOW()+365,B10:B10)

The other concern I have is how will excel know to add the entire column pertaining to ABC123 and not just pick one row?

Thanks for the help

GBSwiss

 

[Scott T]

Try

Excel 2010
	A	B
1	Portfolio	Maturing < 1yr
2	ABC123	100
3	DEF345	1500
4	GHI678	500
Sheet7

Cell Formulas
Range	Formula
B2	=SUMIFS(Sheet8!$B$2:$B$11,Sheet8!$A$2:$A$11,A2,Sheet8!$C$2:$C$11,"<"&NOW()+365)
B3	=SUMIFS(Sheet8!$B$2:$B$11,Sheet8!$A$2:$A$11,A3,Sheet8!$C$2:$C$11,"<"&NOW()+365)
B4	=SUMIFS(Sheet8!$B$2:$B$11,Sheet8!$A$2:$A$11,A4,Sheet8!$C$2:$C$11,"<"&NOW()+365)

Excel 2010
	A	B	C
1	Portfolio	Market Value	Maturity
2	ABC123	100	6/15/2019
3	ABC123	200	7/31/2019
4	ABC123	100	2/15/2018
5	DEF345	600	2/28/2019
6	DEF345	900	3/15/2018
7	DEF345	600	2/1/2018
8	GHI678	400	5/31/2018
9	GHI678	700	8/1/2019
10	GHI678	800	11/30/2019
11	GHI678	100	2/1/2019
Sheet8

 

[gbswiss]

Thank you so so much!

 

[Scott T]

You are welcome