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

XcelRookie

New Member
Joined
Sep 5, 2017
Messages
5
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:
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]DATE[/TD]
[TD]P/L[/TD]
[TD][/TD]
[TD]Lookup Value[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]5-Sep-17[/TD]
[TD]$-92.80[/TD]
[TD][/TD]
[TD]31-Aug-17[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]23-Aug-17[/TD]
[TD]$147.70[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]$0.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]31-Aug-17[/TD]
[TD]$-130.84[/TD]
[TD][/TD]
[TD]Lookup Result[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]23-Aug-17[/TD]
[TD]$142.30[/TD]
[TD][/TD]
[TD]$159.16 (This is what the[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]1-Sep-17[/TD]
[TD]$164.84[/TD]
[TD][/TD]
[TD]formula should return)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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!
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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
ABCD
1DATEP/LLookup Value
25-Sep-17($92.80)31-Aug-17
323-Aug-17$147.70
4$0.00
531-Aug-17($130.84)
623-Aug-17$142.30159.16
71-Sep-17$164.84159.16
Sheet
 
Upvote 0
You're welcome. Thanks for the feedback. The SUMIFS I believe is a little faster and more efficient than the SUMPRODUCT.
 
Upvote 0

Forum statistics

Threads
1,223,910
Messages
6,175,318
Members
452,634
Latest member
cpostell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top