CODEWITHSUNDEEP
excelexcel-formula
user3024007
user3024007

Reputation: 283

Find first match in table range

I have this working formula that I'd like to expand for more flexibility:

=SUMIF(MyTable[Date],"="&$D5,MyTable[Account Balance])

Occasionally, D5 will not have a match in MyTable[Date] range. I'd like it to do "="&$D5+1 and then +1... and so on until first match in the range is located.

Upvotes: 2

Views: 126

Answers (1)

user4039065
user4039065

Reputation:

The next date can be easily located on a sorted or unsorted range of dates using a COUNTIF function to rank and passing that result into a LARGE function as the k parameter.

In the following, I've used SUMIFS function syntax to provide more universality than SUMIF function (e.g. the parameters are necessarily reversed).

The standard formula in G5 is,

=SUMIFS(MyTable[account balance], MyTable[date], LARGE(MyTable[date], COUNTIF(MyTable[date], ">="&F5)))

Fill down as necessary.

    sumifs_next_date

If you have blanks in the [Account Balance] column that need to be discarded, the COUNTIF function will have to become a COUNTIFS function and the LARGE function will have to become an AGGREGATE¹ function. In G5 above as,

=SUMIFS(MyTable[account balance], MyTable[date], AGGREGATE(14, 6, MyTable[date]/(MyTable[account balance]<>""), COUNTIFS(MyTable[date], ">="&F5, MyTable[account balance], "<>")))

¹ The AGGREGATE function was introduced with Excel 2010. It is not available in earlier versions.

Upvotes: 3

Related Questions