How to use SUMPRODUCT with dates
Requirement:
If in a series of dates and corresponding cash flows (say: 12/31/2018 - 12/31/2033) you want to use sumproduct to return the cash flow for a date not in the series (say: 12/31/2016), can SUMPRODUCT return the cash flow ties to 12/31/2018 (oldest and therefore closest date)?
Solution:
Say your dates are listed in range A2:A17
in cell B2 =DATE(2016,12,31)
In order to get the closest date ...you could have in cell C2 the following Array Formula
Code:
=MIN(IF(A2:A17>B2,A2:A17))
Obtained from the OzGrid Help Forum.
Solution provided by Carim.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to sum up values in a date range |
| How to sum up columns in each row and highlight until that value |
| How to use SUMPRODUCT with INDEX MATCH multiple criteria |
| How to set up a pop up message when sum of columns exceeds X |
| How to use VBA to SUM result in last row in columns A:A |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.
Gallery
