Thanks guys -
So I'm going to try and lay it all out below, hopefully its not too in depth.
I do some error tracking and quality control/loss mitigation underwriting, and part of what we're doing is compiling errors made by departments that lead to losses, and then identifying any trends we see. We have two tables tracking data, one from our loss mitigation department and the other from our audit department. The two tables of input data are as follows:
Meeting Date
| GrI Loan
| Accountable Party
| Deficiency Type
|
5/17/2013
| 1000
| Closing
| **Documentation: ARM Data Incorrect
|
5/17/2013
| 1001
| Underwriting/POD
| **Income: Misc.
|
5/17/2013
| 1002
| Underwriting/POD
| **Liabilities: Misc, **Documentation: Misc.
|
4/19/2013
| 1003
| Underwriting/POD
| **Collateral: Incorrect Appraisal Form Completed
|
4/19/2013
| 1004
| Underwriting/POD
| **Collateral: Misc.
|
1/31/2013
| 1005
| POD/GFE Desk/Closing
| **Documentation: TIL Incorrect
|
1/31/2013
| 1006
| Underwriting/POD/Closing
| **Eligibility: Trust not properly documented/Ineligible Trust
|
1/17/2013
| 1007
| Underwriting/POD
| **Income: Employment History , **Income: Salaried Income Overstated
|
12/6/2012
| 1008
| Underwriting/POD
| **Credit: Late payment reflected on history
|
12/6/2012
| 1009
| Underwriting
| **Income: Self Employed Income Documents Missing/Unclear, **Income: Self Employed Income overstated
|
<tbody>
</tbody>
Meeting Date
| GRI Loan Number
| Accountable Party
| Deficiency Type
|
5/17/2013
| 1100
| Underwriting/POD/Closing
| **Income: Misc.
|
5/17/2013
| 1101
| Underwriting/POD
| **Income: Ineligible income type, **Income: Rental Income Improperly Calculated/Documented, **Collateral: Misc.
|
5/17/2013
| 1102
| Underwriting/POD
| **AUS: Misc., **Liabilities: Property not documented as free and clear, **Income: Misc., **Liabilities: Taxes / Insurance Incorrect or not properly documented
|
5/17/2013
| 1103
| Underwriting/POD/Closing
| **Income: Employment History
|
5/17/2013
| 1104
| Underwriting/POD
| **Income: Ineligible income type
|
5/17/2013
| 1105
| Underwriting/POD
| **Income: Employment Type Incorrect
|
5/17/2013
| 1106
| Underwriting/POD/Closing
| **Income: Misc., **Liabilities: Liability's corresponding property not Documented, **Documentation: Misc.
|
5/17/2013
| 1107
| Underwriting/POD
| **Income: Misc., **AUS: Misc.
|
5/17/2013
| 1108
| Underwriting/POD
| **Liabilities: Property not documented as free and clear, **Liabilities: Liability's corresponding property not Documented, **AUS: Misc., **Income: Misc.
|
<tbody>
</tbody>
and then I track them in this table, tallying the error based on department, meeting date, and type of error. As you can see the deficiency type can be one or more of any of the items in the dropdown list, which is what has made this difficult to do with pivot tables (at least for me).
Deficiency Type
| 14-Feb
| 21-Feb
| 28-Feb
| 7-Mar
| 14-Mar
| 21-Mar
| 29-Mar
| 5-Apr
| 12-Apr
| 19-Apr
| 26-Apr
| 3-May
| 10-May
| 17-May
| Totals
| Percentage
|
ASSETS
| 2
| 5
| -
| 11
| 1
| 3
| 2
| -
| 1
| 1
| 8
| 2
| -
| -
| 44
| 18.03%
|
**Assets: Business Funds used not properly documented
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| 1
| 0.41%
|
**Assets: Funds to Close from Unverified Account
| -
| 1
| -
| -
| -
| -
| -
| -
| -
| -
| 1
| -
| -
| -
| 2
| 0.82%
|
**Assets: Gift Not Properly Documented
| 1
| -
| -
| -
| -
| -
| -
| -
| -
| -
| 2
| -
| -
| -
| 4
| 1.64%
|
**Assets: Earnest Money not properly sourced
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| 2
| 0.82%
|
**Assets: Input to AUS Incorrectly
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| 0.00%
|
**Assets: Insufficient Assets Verified
| 1
| 2
| -
| 9
| 1
| -
| 1
| -
| 1
| -
| 1
| -
| -
| -
| 16
| 6.56%
|
**Assets: Large Deposits/Transfers not properly documented
| -
| 1
| -
| 1
| -
| 3
| 1
| -
| -
| 1
| 2
| 1
| -
| -
| 13
| 5.33%
|
**Assets: Misc.
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| 1
| -
| -
| -
| 1
| 0.41%
|
**Assets: Missing Statement/Missing Statement Pages
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| 1
| 1
| -
| -
| 2
| 0.82%
|
**Assets: No Proof of Liquidation
| -
| 1
| -
| 1
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| 3
| 1.23%
|
AUS
| 1
| -
| -
| 3
| -
| -
| 1
| 3
| -
| 1
| 1
| 1
| 3
| 4
| 22
| 9.02%
|
**AUS: Case Number not Entered
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| 1
| 0.41%
|
**AUS: Closed on Invalid, Ineligble, Caution, or Refer
| -
| -
| -
| -
| -
| -
| -
| 1
| -
| -
| 1
| -
| 1
| -
| 3
| 1.23%
|
**AUS: Misc.
| -
| -
| -
| 3
| -
| -
| 1
| -
| -
| -
| -
| 1
| 1
| 4
| 11
| 4.51%
|
**AUS: Not re-ran after changes
| -
| -
| -
| -
| -
| -
| -
| 1
| -
| 1
| -
| -
| 1
| -
| 3
| 1.23%
|
**AUS: Ran as Incorrect Product Type
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| 1
| 0.41%
|
**AUS: Ran as Incorrect Property Type
| 1
| -
| -
| -
| -
| -
| -
| 1
| -
| -
| -
| -
| -
| -
| 3
| 1.23%
|
COLLATERAL
| -
| -
| 1
| -
| 1
| 4
| -
| 2
| 1
| 2
| 1
| 1
| -
| 1
| 17
| 6.97%
|
**Collateral: Comparable Properties Insufficient
| -
| -
| -
| -
| -
| -
| -
| 1
| -
| -
| -
| -
| -
| -
| 3
| 1.23%
|
**Collateral: Incorrect Appraisal Form Completed
| -
| -
| -
| -
| -
| -
| -
| -
| -
| 1
| -
| -
| -
| -
| 1
| 0.41%
|
**Collateral: Misc.
| -
| -
| 1
| -
| 1
| 3
| -
| -
| -
| 1
| -
| 1
| -
| 1
| 9
| 3.69%
|
**Collateral: Missing Necessary Comments/Photos
| -
| -
| -
| -
| -
| -
| -
| -
| 1
| -
| 1
| -
| -
| -
| 2
| 0.82%
|
**Collateral: No Final Inspection as Required
| -
| -
| -
| -
| -
| 1
| -
| -
| -
| -
| -
| -
| -
| -
| 1
| 0.41%
|
**Collateral: Property not documented as Free and Clear
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| 0.00%
|
**Collateral: Significant Adjustments not Addressed
| -
| -
| -
| -
| -
| -
| -
| 1
| -
| -
| -
| -
| -
| -
| 1
| 0.41%
|
CREDIT
| -
| 4
| 1
| 2
| -
| 1
| 2
| -
| -
| -
| -
| -
| 1
| -
| 15
| 6.15%
|
**Credit: Disputed accounts >$500 <24 months old not manually downgraded
| -
| 1
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| 2
| 0.82%
|
**Credit: Judgments on Credit Report not evidenced as Satisfied
| -
| -
| 1
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| 3
| 1.23%
|
**Credit: Late payment reflected on history
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| 1
| 0.41%
|
**Credit: Misc.
| -
| 3
| -
| 2
| -
| 1
| 2
| -
| -
| -
| -
| -
| 1
| -
| 9
| 3.69%
|
DOCUMENTATION
| -
| -
| 1
| 5
| -
| 3
| 3
| -
| -
| -
| 1
| 4
| 2
| 4
| 26
| 10.66%
|
**Documenation: GFE Incorrect
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| 1
| -
| 1
| 0.41%
|
**Documentation: ARM Data Incorrect
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| 1
| -
| -
| -
| 1
| 0.41%
|
**Documentation: Expired
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| 1
| 0.41%
|
**Documentation: Incorrect Dates on Closing Docs
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| 0.00%
|
**Documentation: Misc.
| -
| -
| -
| -
| -
| 1
| -
| -
| -
| -
| -
| 2
| -
| 3
| 7
| 2.87%
|
**Documentation: Missing Documents
| -
| -
| 1
| 5
| -
| 2
| 3
| -
| -
| -
| -
| 2
| 1
| 1
| 16
| 6.56%
|
**Documentation: TIL Incorrect
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| 0.00%
|
ELIGIBILITY
| -
| -
| -
| 5
| 1
| 3
| -
| 2
| -
| -
| 5
| 5
| 1
| -
| 26
| 10.66%
|
**Eligibility: Bankruptcy, Short Sale, DIL, or Foreclosure not sufficiently seasoned
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| 1
| -
| -
| -
| 1
| 0.41%
|
**Eligibility: Homepath related Eligibility Issue
| -
| -
| -
| -
| -
| -
| -
| 1
| -
| -
| 1
| -
| -
| -
| 2
| 0.82%
|
**Eligibility: Ineligible due to Credit
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| 0.00%
|
**Eligibility: Max LTV/CLTV exceeded
| -
| -
| -
| -
| -
| 2
| -
| -
| -
| -
| -
| -
| -
| -
| 2
| 0.82%
|
**Eligibility: Misc
| -
| -
| -
| 1
| 1
| 1
| -
| -
| -
| -
| -
| -
| 1
| -
| 6
| 2.46%
|
**Eligibility: Multiple Financed Properties Issue
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| 3
| 5
| -
| -
| 8
| 3.28%
|
**Eligibility: Occupancy Incorrect/Misrepresented
| -
| -
| -
| 4
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| 5
| 2.05%
|
**Eligibility: Trust not properly documented/Ineligible Trust
| -
| -
| -
| -
| -
| -
| -
| 1
| -
| -
| -
| -
| -
| -
| 2
| 0.82%
|
INCOME
| 1
| 4
| -
| 14
| 2
| 5
| 1
| -
| 4
| -
| 8
| 3
| 4
| 15
| 77
| 31.56%
|
**Income: Commission / Bonus Income Documents Missing/Unclear
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| 1
| 0.41%
|
**Income: Commission / Bonus Income Overstated
| -
| 1
| -
| -
| -
| -
| -
| -
| 1
| -
| -
| -
| -
| -
| 3
| 1.23%
|
**Income: Employment History
| -
| -
| -
| 1
| -
| -
| -
| -
| -
| -
| -
| -
| -
| 2
| 4
| 1.64%
|
**Income: Employment Type Incorrect
| -
| -
| -
| -
| 1
| 2
| -
| -
| -
| -
| -
| -
| -
| 1
| 4
| 1.64%
|
**Income: Ineligible income type
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| 1
| -
| -
| 2
| 3
| 1.23%
|
**Income: Misc.
| -
| 1
| -
| 1
| -
| 1
| -
| -
| -
| -
| 1
| 1
| 1
| 9
| 15
| 6.15%
|
**Income: Rental Income Improperly Calculated/Documented
| -
| -
| -
| 4
| -
| -
| 1
| -
| -
| -
| -
| 1
| -
| 1
| 7
| 2.87%
|
**Income: Salaried Income Documents Missing/Unclear
| -
| -
| -
| 3
| -
| 1
| -
| -
| -
| -
| 2
| -
| 1
| -
| 11
| 4.51%
|
**Income: Salaried Income Overstated
| 1
| -
| -
| 1
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| 4
| 1.64%
|
**Income: 2106 not deducted
| -
| 1
| -
| -
| -
| -
| -
| -
| 1
| -
| 1
| -
| 2
| -
| 6
| 2.46%
|
**Income: Self Employed Income Documents Missing/Unclear
| -
| -
| -
| 2
| 1
| 1
| -
| -
| 1
| -
| 1
| 1
| -
| -
| 9
| 3.69%
|
**Income: Self Employed Income overstated
| -
| -
| -
| 2
| -
| -
| -
| -
| -
| -
| 1
| -
| -
| -
| 6
| 2.46%
|
**Income: Self Employed mortgages, notes payable in one year not deducted
| -
| 1
| -
| -
| -
| -
| -
| -
| 1
| -
| 1
| -
| -
| -
| 4
| 1.64%
|
LIABILITIES
| -
| 3
| 2
| 4
| 4
| 5
| 4
| 1
| 2
| -
| 2
| 6
| 3
| 10
| 51
| 20.90%
|
**Liabilities: Divorce Decree Liabilities not included
| -
| -
| 1
| 1
| 1
| -
| -
| -
| -
| -
| -
| 1
| -
| -
| 4
| 1.64%
|
**Liabilities: Liability's corresponding property not Documented
| -
| -
| -
| -
| -
| 1
| 1
| -
| -
| -
| -
| 2
| -
| 4
| 8
| 3.28%
|
**Liabilities: Misc
| -
| -
| 1
| -
| -
| -
| 1
| -
| -
| -
| -
| 1
| -
| 3
| 6
| 2.46%
|
**Liabilities: Payment / Liability terms not verified
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| 1
| -
| -
| -
| 2
| 0.82%
|
**Liabilities: Property not documented as free and clear
| -
| 2
| -
| 1
| -
| 1
| -
| -
| 1
| -
| -
| 2
| 3
| 2
| 12
| 4.92%
|
**Liabilities: Second Lien not Subordinated / Documented as Paid
| -
| -
| -
| 1
| 1
| 1
| -
| -
| -
| -
| -
| -
| -
| -
| 3
| 1.23%
|
**Liabilities: Soft Pull not within 15 Days of Closing
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| -
| 0.00%
|
**Liabilities: Tax Return/Tax Transcript Liabilities not included
| -
| -
| -
| 1
| 1
| -
| 1
| 1
| -
| -
| -
| -
| -
| -
| 5
| 2.05%
|
**Liabilities: Taxes / Insurance Incorrect or not properly documented
| -
| 1
| -
| -
| -
| 1
| 1
| -
| 1
| -
| 1
| -
| -
| 1
| 7
| 2.87%
|
**Liabilities: Undisclosed Liability
| -
| -
| -
| -
| 1
| 1
| -
| -
| -
| -
| -
| -
| -
| -
| 4
| 1.64%
|
Overall Total
| 4
| 16
| 5
| 44
| 9
| 24
| 13
| 8
| 8
| 4
| 26
| 22
| 14
| 34
| 244
| 113.93%
|
<tbody>
</tbody>
The formula that tallies each entry is as follows:
=(SUMPRODUCT(--(ISNUMBER(FIND($E35,Table2[Deficiency Type]))),--(Table2[Meeting Date]=DATE(2013,2,14)), --(ISNUMBER(FIND("Underwriting",Table2[Accountable Party])))))+(SUMPRODUCT(--(ISNUMBER(FIND($E35,Table3[Deficiency Type]))),--(Table3[Meeting Date]=DATE(2013,2,14)), --(ISNUMBER(FIND("Underwriting",Table3[Accountable Party])))))+(SUMPRODUCT(--(ISNUMBER(FIND($E35,Table5[Deficiency Type]))),--(Table5[Meeting Date]=DATE(2013,2,14)), --(ISNUMBER(FIND("Underwriting",Table5[Accountable Party])))))+(SUMPRODUCT(--(ISNUMBER(FIND($E35,Table513[Deficiency Type]))),--(Table513[Meeting Date]=DATE(2013,2,14)), --(ISNUMBER(FIND("Underwriting",Table513[Accountable Party])))))
It is so lengthy because there are three criteria each entry must meet to be tallied (meeting date, accountable department, and deficiency type) and it adds the results from the two different tables of data (loss mitigation and audit). Also, once the issues are resolved they are pulled to a resolved list, but we didn't want the information to fall off the tracking list above, so I had to include that in the formula as well. Essentially, the formula is tallying based on three criteria for four different tables and adding all the results together.
I know that there may have been an easier way to do this possibly using the countifs function, but I wasn't able to get it working, and as I'm sure you can tell as this point I am an excel novice.
Sorry for the lengthy post, any help is greatly appreciated.
***EDIT - Also I forgot the important part. So what management would like to do (I'm not entirely sure its possible with the way these items are being tallied) is to hover over one of the entries in the table, ie **
Assets: Insufficient Assets Verified identified on March 7, and see the nine loan numbers from the 9 errors tallied in that cell.
