1 MATCH, 2 INDEXES; How do I sum the results without repeating the formula?

[lmoseley7]

I have a table that is a rent schedule. This table has columns for 'Start Date', 'Base Rent', 'Building Cards', 'Parking Permits', 'Service Agreements', and 'OPEX rates'. The start dates are generally the first of a month, but they are not every month in the time period, only those months where changes occur in any of the columns to the right of 'Start Date'. This schedule is referenced by another portion of the workbook that includes every month during the time period. I'm using a Match function to determine the last date that was prior to or equal to the date in question to determine what monthly charges apply. Because I'm combining the charges from 'Building Cards' and 'Parking Permits', I've had to repeat the basic INDEX & MATCH formula to return the related value from both columns, and then sum the two values together. Alternatively, I could add another column that combines these two values together and just return that value, but when possible I try to make things hard on myself in case I come across a situation where I can't add a helper column and it gives me an example case to learn from.

Here is the formula that is working:

=IF($B44-EOMONTH($B44,0)=0,INDEX(AR_RentSched[Building Cards],MATCH($A44,AR_RentSched[Start Date],1)),0)
+IF($B44-EOMONTH($B44,0)=0,INDEX(AR_RentSched[Parking Permits],MATCH($A44,AR_RentSched[Start Date],1)),0)

In the formula above, $B44 contains the date we are testing for. I'm first looking to see if the date is as of the end of the month. If not, we wouldn't want to calculate monthly charges, so we return a 0. 'AR_RentSched' is the name of the table.

I'd like to not have to repeat the formula just to combine the results from two columns where there is a match on the row. I'm guessing this might require an array formula, but I haven't been able to find one and my attempts to create one have not gone well. Is it possible to do what I want without having to add a helper column or repeat the INDEX and MATCH formula?

 

[ValuationMan]

I'm not sure if this helps, but you can simplify your formula.

=IF($B44-EOMONTH($B44,0)=0,INDEX(AR_RentSched[Building Cards] + AR_RentSched[Parking Permits],MATCH($A44,AR_RentSched[Start Date],1)),0)

 

[Eric W]

Try this:

=IF($B44=EOMONTH($B44,0),SUM(SUMIF(AR_RentSched[Start Date],$A44,INDIRECT("AR_RentSched"&{"[Building Cards]","[Parking Permits]"}))),0)

This assumes that the Start Dates in the table are unique.

 

[DanteAmor]

Try this:

=IF($B44=EOMONTH($B44,0),SUMPRODUCT((AR_RentSched[Start Date]=$A44)*(AR_RentSched[Building Cards]+AR_RentSched[Parking Permits])))

 

[lmoseley7]

I'm not sure if this helps, but you can simplify your formula.

=IF($B44-EOMONTH($B44,0)=0,INDEX(AR_RentSched[Building Cards] + AR_RentSched[Parking Permits],MATCH($A44,AR_RentSched[Start Date],1)),0)

All I had to do was add the two columns together??? Trying to make things too hard again I guess. What a great fix, thanks.

 

[lmoseley7]

Try this:

=IF($B44=EOMONTH($B44,0),SUM(SUMIF(AR_RentSched[Start Date],$A44,INDIRECT("AR_RentSched"&{"[Building Cards]","[Parking Permits]"}))),0)

This assumes that the Start Dates in the table are unique.

Subtle change in the original equality statement, but it does reduce the overall footprint by eliminating the "-" and a "0"...you are a craftsman.

I like the use of INDIRECT and the array of choices {}. I've used that in a similar manner when designing spreadsheets to create formula templates that pull their information from within the spreadsheet, allowing me to write one formula to be used across an entire spreadsheet.

The one issue I have with this solution is that I'm not going to find a match to $B44 always because the table that will include the formula is a rent amortization table that includes a row for every month of the lease across 10+ year leases. The rent schedule only includes particular dates within the period covering 10+ years. This allows me to keep the number of entries in the rent schedule to just those times when rates changed, instead of having a row for every month, which is what the rent amortization table is. That's where the Match feature to find the largest value, in this case a date, that is less than or equal to my start date is necessary. If there was a way to incorporate a MATCH inside a SUMIF or somehow perform the same function it might work.

 

[lmoseley7]

Try this:

=IF($B44=EOMONTH($B44,0),SUMPRODUCT((AR_RentSched[Start Date]=$A44)*(AR_RentSched[Building Cards]+AR_RentSched[Parking Permits])))

Similar to Eric W's post above, your version did not take into account that the Rent Schedule table would not include all dates from the detail amortization table. I do love a good SUMPRODUCT formula though, so thanks for that. I tried to figure out how if I changed your formula to be "<=$A44", how I could limit it to only return the last value that was less than or equal to our date. I ended up using OFFSET AND MATCH to find a date that I could compare to the AR_RentSched[Start Date] array to return a series of True and False statements to complete the SUMPRODUCT logic. This doesn't make the formula shorter, but it did work. Here is what I came up with to conform your example to my needs.

=IF($B64=EOMONTH($B64,0),SUMPRODUCT((OFFSET(AR_RentSched[Start Date],MATCH($B64,AR_RentSched[Start Date],1)-1,0,1,1)=AR_RentSched[Start Date])
*(AR_RentSched[Building Cards]+AR_RentSched[Parking Permits])),0)

 

[lmoseley7]

Thanks everyone for the help!