A formula which calculates the total annual rental of a service and next contract end date

Hi there,

I hope you are well!

I am trying to find a formula that calculates the total annual spend of a set of services which a customer takes out. In addition I am trying to find a formula that identifies the next contract end date of these services.

I have attached a spreadsheet in with more information. Please feel free to take a look and give it a go.

thanks a lot

Mike

Annual Spend and Next Contract End.xlsx

Put this formula in B21:

Formula:

=B5

and copy down. Incidentally, you must ensure that you have no spurious spaces in cell B5 to B7 - there is a single space after "Internet". Or, you could change the formula to this:

Formula:

=TRIM(B5)

Then you can have this formula in C21:

Formula:

=SUMIF(C$12:C$16,B21,D$12:D$16)

and this array* formula in D21:

Formula:

=MIN(IF((C$12:C$16=B21)*(C$12:C$16>TODAY()),E$12:E$16))

then copy these down.

*An array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE), instead of the usual <Enter>.

Hope this helps.

Pete

Hi pete

Just one more thing, if no services are selected then I get the standard "jan00" in putted automatically by excel. Is there anyway to adjust the formula so if no services are selected then the cell remains blank?

Which cell(s) does that occur in?

Pete

Hi Pete

I have uploaded an updated version of the spreadsheet with the jan 00 issue. In this example the customer has taken out 5 lots of internet and no ethernet or phones. Therefore for cells D21 and D23 the cell has Jan00 in it.

Annual Spend and Next Contract End.xlsx

Thanks

Mike

Okay, put this array* formula in D21:

Formula:

=IF(MIN(IF((C$12:C$16=B21)*(C$12:C$16>TODAY()),E$12:E$16))=0,"",MIN(IF((C$12:C$16=B21)*(C$12:C$16>TODAY()),E$12:E$16)))

then copy down. It is basically:

=IF(your_formula = 0, "", your_formula)

*Use CSE, as previously advised.

Hope this helps.

Pete

Hi Pete

Thanks a lot for that. That works perfectly. Much appreciated!!

Mike

Thats brilliant Pete. Again thanks for all of your help. It is much appreciated!!

Mike