Using INDEX MATCH to Return and Sum Multiple Values that Meet <=Date Criteria

[XcelRookie]

Not sure if INDEX MATCH is the right function for this - I've struggled to formulate a query to sum multiple values that meet the <= Date criteria. Here's the ask:

	A	B	C	D
1	DATE	P/L		Lookup Value
2	5-Sep-17	$-92.80		31-Aug-17
3	23-Aug-17	$147.70
4		$0.00
5	31-Aug-17	$-130.84		Lookup Result
6	23-Aug-17	$142.30		$159.16 (This is what the
7	1-Sep-17	$164.84		formula should return)

<tbody>
</tbody>

1. In Cell D6, I need a formula that will sum all the values in Column B for all the dates in Column A that are LESS THAN OR EQUAL TO the date in Cell D2 (which is entered in Date Format).

2. Some of the cells in the Date Column will be blank so the formula needs to accommodate that.

3. Would be a bonus if the formula could handle dynamic ranges for the columns so the column range would automatically grow as the spreadsheet grows.

Note: It is not practical to sort the dates in ascending or descending order (to take advantage of Match Type) as these fields are part of a large and complex spreadsheet.

Have tried this formula but it returns the values for Aug 31 only: =INDEX(B2:B7,MATCH(D2,A2:A7,0))

Any help or suggestions greatly appreciated!

 

[AhoyNC]

Try SUMIFS function if you have a later ver. of Excel. Otherwise look at the SUMPRODUCT function.
If your data rows are only going to be increasing in size then look at setting your data range as a table to make it dynamic.

Excel Workbook
	A	B	C	D
1	DATE	P/L		Lookup Value
2	5-Sep-17	($92.80)		31-Aug-17
3	23-Aug-17	$147.70
4		$0.00
5	31-Aug-17	($130.84)
6	23-Aug-17	$142.30		159.16
7	1-Sep-17	$164.84		159.16
Sheet

 

[XcelRookie]

Many thanks, AhoyNC. Both formulas work beautifully!

 

[AhoyNC]

You're welcome. Thanks for the feedback. The SUMIFS I believe is a little faster and more efficient than the SUMPRODUCT.