If Statement & Data Validation Help

[scoste]

I am seeking help for this workbook that has been a pain in my side.

Overall the sheet attached is a dashboard of a main datasheet with all the information we need. The goal is to have a running account for every year to account for the total number of loans, total volume, gross fee and total income for all loan for all properties for each year. This formula I understand how to create, what is making this difficult is I have added in 2 different data validation lists to come up with each loan officers total loans, volume, fee and income which needs to be broken down by property type.

Also needed is the ability to add new advisors to the list along with new data in the master sheet which will not break the formula and be included in the new year when we add.

The current formula I have is for "# of Loans": =IF(AND(C12="ALL LOANS",D12="ALL PROPERTIES"),COUNTIF('Main - Data'!$A$2:$A$369,B12),IF(C12=Types,COUNTIFS('Main - Data'!$A$2:$A$369,B12,'Main - Data'!$B$2:$B$369,C12,'Main - Data'!$N$2:$N$369,D12),"Not Working"))

The "Not Working" is used to test the formula and will be taken out once one of you excel genius can assist me. The formulas for Volume, Gross Fee and Income will all be different and I would love help with these as well. Down the line I would be looking to break down the data even further so the formulas hopefully can accommodate this.

Any help would be much appreciated!