Matching in the same column on excel to multiply two rows of data together for each property

[Harry2812]

I have a table where I would like the formula to identify the property first i.e property A, B, C before identifying if it is leasehold or freehold in column 2 and then to multiply cost security per FTE by number of FTE to provide end result. I need the formula to pick up new properties as the data table is updated.

Please can you help 

[#2 · Accepted solution]

@Harry2812 

The relatively easy part is the last, for the formula to automatically pick up new properties.

One would have to make a few assumptions though about what you're doing here. The table is not exactly a standard table in that (for example) the numbers in Yr1, Yr2 and Yr3 sometimes count people (FTE being, presumably, Full Time Employees, although that's another thing you've left to your readers to assume); sometimes the numbers count square meters, sometimes Energy Cost or Security Cost per FTE. And so forth. That kind of inconsistency can wreak havoc with formulas, where consistency is expected.

My point is to ask a few questions:

• First, could you give an example of the calculation you're asking for--it probably is in fact fairly straight forward, once terms are clearly defined
• Second, it would appear that you are ONLY asking for the calculation of the line "Cost security per FTE", yet it's not clear what the relationship is between that line and the "Number of FTE" line. Does one influence the other? If so, how?
• Third, What's the effect if it's "Freehold" instead of "Leasehold"?
• Finally, please post a copy of the actual spreadsheet from which you took the picture; that way you help us help you even further by not expecting us to re-create it

[#3]

Hi John,

Yes this is not a standard table and you are correct in stating FTE is Full Time Employees.

Of the property table I would like to create a formula that will summarise the security cost per year of all leasehold properties and then security cost per year of all freehold properties once I have this formula I will then do the same for Energy cost per year (Product of 'Number of FTE' and 'Energy cost per FTE (£/yr)

I have forwarded you a copy of the spreadsheet.

Thanks for your help

[#4]

I just sent you a new and improved version of the spreadsheet, so I hope you come back and see this message.