Help with Multiple Data Validation Inputs and If Statements

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!

I have modified the sheet you uploaded.
Please note I have only modified row 13, so you can see how the formulas are different.
I also have a section to the right of your data that I used as testing.

So I was re-reading some of the other things you wanted instead of just fixing the formula.
If you take the $2 and $369 row declarations out of the formula and just refer to the entire column $A:$A you can continue to add rows on the main data sheet with no problem with the formulas breaking.
The formulas for example will never match any data on row 1 of the main data sheet, unless you type "Year" instead of a year.
Next the formulas will also not break if you add more items to your lists. The formulas provided do not refer to the lists themselves only the cells on the dashboard.
You will just need to update your named ranges to include your new entries on the lists page as you add to them, then your dropdowns will still function.
I have updated this sheet with taking out the $ for the rows.

YOUR AMAZING, this is exactly what I needed for this column. I tried to adjust for the volume, gross and income using SUMIF and SUMIFS but was unable to make the transfer work. Would be be possible to help me out for these?

Also, how do you suggest, if I divi up the data later on to months and cities?

I did the volume gross and income columns as well with the sumifs. Each of them seemed to be working correctly for me.

loans =IF(AND(C13="ALL LOANS",D13="ALL PROPERTIES"),COUNTIFS('Main - Data'!$A:$A,B13),IF(AND(C13="ALL LOANS",D13<>"ALL PROPERTIES"),COUNTIFS('Main - Data'!$A:$A,B13,'Main - Data'!$H:$H,D13),IF(AND(C13<>"ALL LOANS",D13="ALL PROPERTIES"),COUNTIFS('Main - Data'!$A:$A,B13,'Main - Data'!$B:$B,C13),IF(AND(C13<>"ALL LOANS",D13<>"ALL PROPERTIES"),COUNTIFS('Main - Data'!$A:$A,B13,'Main - Data'!$B:$B,C13,'Main - Data'!$H:$H,D13),"No match"))))

volume =IF(AND(C13="ALL LOANS",D13="ALL PROPERTIES"),SUMIFS('Main - Data'!$G:$G,'Main - Data'!$A:$A,B13),IF(AND(C13="ALL LOANS",D13<>"ALL PROPERTIES"),SUMIFS('Main - Data'!$G:$G,'Main - Data'!$A:$A,B13,'Main - Data'!$H:$H,D13),IF(AND(C13<>"ALL LOANS",D13="ALL PROPERTIES"),SUMIFS('Main - Data'!$G:$G,'Main - Data'!$A:$A,B13,'Main - Data'!$B:$B,C13),IF(AND(C13<>"ALL LOANS",D13<>"ALL PROPERTIES"),SUMIFS('Main - Data'!$G:$G,'Main - Data'!$A:$A,B13,'Main - Data'!$B:$B,C13,'Main - Data'!$H:$H,D13),"No match"))))

gross =IF(AND(C13="ALL LOANS",D13="ALL PROPERTIES"),SUMIFS('Main - Data'!$J:$J,'Main - Data'!$A:$A,B13),IF(AND(C13="ALL LOANS",D13<>"ALL PROPERTIES"),SUMIFS('Main - Data'!$J:$J,'Main - Data'!$A:$A,B13,'Main - Data'!$H:$H,D13),IF(AND(C13<>"ALL LOANS",D13="ALL PROPERTIES"),SUMIFS('Main - Data'!$J:$J,'Main - Data'!$A:$A,B13,'Main - Data'!$B:$B,C13),IF(AND(C13<>"ALL LOANS",D13<>"ALL PROPERTIES"),SUMIFS('Main - Data'!$J:$J,'Main - Data'!$A:$A,B13,'Main - Data'!$B:$B,C13,'Main - Data'!$H:$H,D13),"No match"))))

income =IF(AND(C13="ALL LOANS",D13="ALL PROPERTIES"),SUMIFS('Main - Data'!$L:$L,'Main - Data'!$A:$A,B13),IF(AND(C13="ALL LOANS",D13<>"ALL PROPERTIES"),SUMIFS('Main - Data'!$L:$L,'Main - Data'!$A:$A,B13,'Main - Data'!$H:$H,D13),IF(AND(C13<>"ALL LOANS",D13="ALL PROPERTIES"),SUMIFS('Main - Data'!$L:$L,'Main - Data'!$A:$A,B13,'Main - Data'!$B:$B,C13),IF(AND(C13<>"ALL LOANS",D13<>"ALL PROPERTIES"),SUMIFS('Main - Data'!$L:$L,'Main - Data'!$A:$A,B13,'Main - Data'!$B:$B,C13,'Main - Data'!$H:$H,D13),"No match"))))

to add more checks for months and cities, you can add them in to the things to check in the AND() functions. Just follow the same pattern. =if(and(check1,check2,check3),thing to do if they all are true, next if(and()) check.