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

Portfolio	Maturing < 1yr
ABC123
DEF345
GHI678

<tbody>
</tbody>

Worksheet B

Portfolio	Market Value	Maturity
ABC123	100	6/15/2019
ABC123	200	7/31/2019
ABC123	100	2/15/2018
DEF345	600	2/28/2019
DEF345	900	3/15/2018
DEF345	600	2/1/2018 (today)
GHI678	400	5/31/2018
GHI678	700	8/1/2019
GHI678	800	11/30/2019
GHI678	100	2/1/2019 (1 yr from today)

<tbody>
</tbody>

The result should look like this:

Worksheet A

Portfolio	Maturing < 1yr
ABC123	100
DEF345	1,500
GHI678	500

<tbody>
</tbody>

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