Help on SUMIFS and COUNTIFS

Hi,

I am working on a lease spreadsheet that I have attached in this document.

These are my two problems:

First-->In the "Total Area" row in the lower table I created a SUMIFS function to calculate how much area each lease is taking up based on the year that the lease expires. This function works well for the leases that expire in a specific year, but for the lease that is month to month (MTM; where there is nothing in the "Lease To" column in the upper table) I had to subtract off cell E20 in order to get the correct value in cell I26. The idea is that I could paste a different property with different amounts of leases into this template and have the bottom table automatically update the new information. I have tried this and it works mostly, but the MTM lease never works out because of that subtraction of cell E20. Therefore I think instead of subtracting off E20, I need a COUNTIFS function to make sure the formula only includes the area from the MTM lease.

Second-->The % Area row in the lower table shows how much of the total area the leases that expire in a given year take up. The formula in that row for all of the expiration years works right now, but since I have the denominator (E20) locked in with $$ it doesn't work when I paste a different property in that has a different amount of leases (i.e., "Total Current" area is not located in cell E20 anymore). Therefore, I think I need the denominator for that formula to be a COUNTIFS(?) function that ensures that the denominator is the "Total Current" area for that property (which, in this case is located in cell E20)


("Total Annual Rental Rev", "% Revenue" and "Avg. Rent PSF" can be ignored for now.)

Any help on modifying these 2 formulas would be extremely helpful.

If I've understood correctly, this should work in I26:

Formula:

Please Login or Register  to view this content.

It simply adds another condition that ignores rows where column A = Total Current. i.e. ignores the total row wherever it shall be.

And this should work in I29:

Formula:

Please Login or Register  to view this content.

It uses a lookup to find the total value by finding which row of column A Total Current is found in.

BSB

I got the 3,594 in I26 without needing E20 as follows:

=SUMIFS(E4:E19,G4:G19,"",C4:C19,"<>"&"VACANT")

Obviously the range will need to change if you adding new rows - might be easier if you converted to a Table, which will than add new data automatically?

Can you clarify the second stage, because you ignore the three "vacant" areas when you calculate the Total Area, but now seem to want to include them in the percentage?

Ochimus

Thank You, those formulas helped. However, I want to get to the crux of my problem here so I have attached a full example rent roll as it appeared when I downloaded it.

This property has 16 "Units" as seen in the upper table. However, when I substitute another property into this template, it might only have 10 "Units". This will cause a problem where the far right table with "Annual Rec", "Annual Rent Plus Rec / Area" and "Annual Rent Plus Annual Rec" will have some rows that are "dangling" (it works for this property because that far right table just adds horizontally from the main table) and the summations don't work.

I need to be able to copy and paste from an entirely separate spreadsheet a different property's rent roll into this template and have the lower table and the far right table automatically update their info (and in turn have the bar graph automatically update as well) even if the new property that was pasted in has less (or more) units than this property does in the template I have provided.

The most important part is that the "Total Area", "Total Annual Rental Revenue", "% Area", "% Revenue" and "Avg Rent PSF (Gross)" rows in the lower table are equipped with formulas that will allow a new property's rent roll to be pasted into this spreadsheet and have them automatically update.

I should note that the formulas in the "Total Area" and "% Area" rows from the lower table have already been addressed in this same thread, so the current problems are with the far right table and the other 3 rows in the lower table.

I think in order to fully understand the issue here it would be helpful to see what the template looks like with a rent roll that hare more and less rows. i.e. what it all looks like after you've pasted in but not amended anything else.

BSB

I've attached an updated excel doc with the original template from before as well as the new property, and what happens when I copy and paste it into the template.

I should note that when I copied the new tenant, I copied from cell A8 to cell P22. However, if there is a way to copy and paste the new tenant from the top left hand corner of the spreadsheet that would be great as well

ND,

The only "automatic" way you can paste more than sixteen units is to restructure the entire document.

I would put the "totals" in row 3, and from A4 - T4 down I would convert the data into a Table, with each column a "dynamic" named Range.

That way, the total values adjust however many entries you had, and your formulae will adjust automatically.

Your data in A23 - F25 should also be "automated", as the sample shows ten as "occupied" when the data shows only eight are?

Your expiration table needs to start in W4, and the graph "overlay" W13.

Ochimus