Multiple Criteria IF, INDEX, or VLOOKUP

[chrapm1]

I have a storage rental sheet with multiple pages.

Theres three pages (Financials, Storage Rent, Renters)

The "Renters" Page has the names of the people renting with their rental rates, start dates, end dates.
The "Storage Rent" Page is has the storage units with organized by month with dropdowns so I can select if Rent is (Paid, Due, Vacant)
The "Financials" Page has the overall rental income organize by type of rental and by month.

I am stuck trying to set up a formula on the Financials page to do the following:
Sum all the rent based on Categories (Storage Units, Parking, RV) per month
Criteria that formula needs to check for:
1. If storage unit #X is paid to lookup the rent rate from the renters page
2. Only sum the renters that are "Paid" and active in the month its associated with

I've attached an excel workbook that may help you see what I'm trying to accomplish.

Thanks,

[dflak]

First, a word about tables. You don't need to fill out the blank rows on tables. When you enter anything in on a row immediately below or immediately to the right of a table, it becomes part of the table. It will also copy down, formulas, formats or whatever from the rows above. So I truncated your tables to contain only those rows that actually have data. I also renamed them:

Renters is Table_Renters, and I made units a table Table_Units and I created a payments table, Table_Payments.

I also "cheated" a bit. I used the table names in the data validations, so if you want to validate for Units, use = INDIRECT("Table_Units[Units]"). The list will grow if you add things to the table.

You could scratch your head and work out individual formulas to look up and sum data or let Excel do the dirty work in a pivot table. Pivot tables work IF you can get the data into a good format. That's where the payments table comes in. I'll explain it in a minute.

Fill in the renters' table as you have been doing.

You notice that I added an extra column to the units table to show the type of unit.

The payments table has data in a "normalized" format that is suitable for use with a pivot table.

When a person makes a payment, enter the date in column A. This will open a new line in the table. Enter the tenant's name using the drop-down list in column B. The lookup for this is from the tenants table. The information in columns C & D is looked up based on the tenant's name from the tenants table and the type of unit is looked up based on the unit "name" from the units table.

The data is now ready for a pivot table. The pivot table will grow automatically as time and payments go by. It also has the advantage of keeping monthly totals and a running total for the year and overall.

Use the payment table instead of the Storage Rent sheet to record payments. I think the drop-downs are easier to use than trying to find the intersection of a tenant and a unit. Also you can keep several years of data without ever having to "extend" the matrix.

The pivot automatically takes care of he financials for you.

[chrapm1]

First, a word about tables. You don't need to fill out the blank rows on tables. When you enter anything in on a row immediately below or immediately to the right of a table, it becomes part of the table. It will also copy down, formulas, formats or whatever from the rows above. So I truncated your tables to contain only those rows that actually have data. I also renamed them:

Renters is Table_Renters, and I made units a table Table_Units and I created a payments table, Table_Payments......

dflak,

I've been messing with the pivot tables and am starting to get the hang of it. The last speedbump I am hitting is combining two pivot tables to create a master financial sheet. Or being able to add categories to a pivot table without them being associated with each other.

My goal is to have a financial summary on one pivot table. Showing rental income and expenses sub categorized how I have the current pivot tables. Can this be done with the Income and Expenses being on two separate tables?


I've attached what I have so far.

Thanks,

[dflak]

There is a thing called Power Pivot which is a part of Excel 2016. I've only looked at it once or twice. It's one of several remaining corners of the Excel universe I've not explored. I've only sent a couple of probes

Power pivot will let you combine two tables provided that there is an overlap in the fields in the tables. I suggest you do a web search for some tutorials. It seems easy enough, but involves some manual steps. I prefer to use VB to merge the tables sort of like a UNION query in SQL. I have some "standard" code to do this.