If stmt with vlookup and sumif

The way you have phrased this implies that you want to do the SUMIF first, so try it this way:

IF(COUNTIF('4_DMQ Data'!$C$2:$C$2000,$B2)>0,SUMIF('4_DMQ Data'!$C$2:$C$2000,$B202,'4_DMQ Data'!$F$2:$F$2000)*H202,IF(COUNTIF(SBBT!$J$4:$J$27,B202)>0,VLOOKUP(B202,SBBT!$J$4:$K$27,2,0),""))

Hope this helps.

Pete

Sorry ChemistB, an error message was returned

I am currently unable to upload a sample file but the three simple tabs are as follows:

4_DMQ data

custom4 custom5 gl as_of_date RowCT Book_DMQ
IGNORE_Captured in BV of Resi UB 142292 2012-12-31 4 734
Deferred Loan Fees (GL)-Res M Frontier 142294 2012-12-31 1 736
Deferred Loan Fees (GL)-Res M TAM 142294 2012-12-31 1 738
Deferred Loan Fees (GL)-Res M UB 142294 2012-12-31 1 740
UB 142299 2012-12-31 1 742
UB 142600 2012-12-31 1 744
Fixed Rate (LSBO) UB 142600 2012-12-31 1 746
Non-accrual Resi ACQ Frontier 142780 2012-12-31 2 748
Non-accrual Resi ACQ TAM 142780 2012-12-31 1 750
Deferred Loan Fees (GL)-Res M SBBT 142802 2012-12-31 1 752
Residential ACQ Fixed Frontier 142802 2012-12-31 1 754
Residential ACQ Fixed TAM 142802 2012-12-31 2 756
SBBT Residential Fixed SBBT 142802 2012-12-31 1 758
Deferred Loan Fees (GL)-Res M UB 142848 2012-12-31 1 760
Deferred Loan Fees (GL)-Res M UB 142849 2012-12-31 1 762
5/1 Jumbo Hybrid Arm (LSBO) UB 142850 2012-12-31 1 764
6M Libor Arm (LSBO) UB 142850 2012-12-31 2 766
Fixed Rate (LSBO) UB 142850 2012-12-31 2 768
Non-accrual Resi ACQ Frontier 142850 2012-12-31 2 770
Deferred Loan Fees (GL)-Res M UB 142857 2012-12-31 1 772
Deferred Loan Fees (GL)-Res M UB 142858 2012-12-31 1 774
Deferred Loan Fees (GL)-Res M UB 142859 2012-12-31 1 776
Deferred Loan Fees (GL)-Res M UB 142860 2012-12-31 1 778
Deferred Loan Fees (GL)-Res M UB 142872 2012-12-31 1 780
Deferred Loan Fees (GL)-Res M UB 142874 2012-12-31 1 782
ILS Loan-Cash Reserve(GL) SBBT 143002 2012-12-31 1 784
Non-accrual Consumer ACQ Frontier 143002 2012-12-31 1 786
SBBT Residential Fixed SBBT 143002 2012-12-31 1 788
Home Equity - Fixed UB 143003 2012-12-31 130 790
Home Equity - Variable UB 143003 2012-12-31 2 792
Home Equity ACQ Variable Frontier 143003 2012-12-31 2 794
Non-accrual Consumer ACQ Frontier 143003 2012-12-31 20 796
Deferred Loan Fees (GL)-Res M UB 143004 2012-12-31 5 798


6_GL_compare(orig rows)

Type GL_ACCOUNT_ID GL_DESCRIPTION GL_TOTAL Adjusted GL QRM Contra ? DMQ
Assets 142294 FFIEC Contra - RE ARM Hybrid 1st Liens HFI 22 22 1 2,214
Assets 142600 REAL ESTATE LOANS-PARTIC PURCH 11 11 1 1,490
Assets 142780 RESIDENTIAL MORTGAGE LNS - CONTROL (33) (33) 1 1,498
Assets 142800 RESIDENTIAL LNS - ACQ 40 40 1 34
Assets 142801 PREM DISC - RESIDENTIAL MORTGAGE ACQ 50 50 1 -
Assets 142802 MTM VAL - RESIDENTIAL MORT ACQ 60 60 1 3,020
Assets 142848 RESIDENTIAL - FHLB PROGRAM 70 70 1 760
Assets 142849 RESIDENTIAL - FHLB PROGRAM CONTRA (44) (44) 1 762
Assets 142850 RESIDENTIAL LOANS SERVICED BY OTHERS 80 80 1 3,068
Assets 142857 PARTIAL CHRG OFF - RE SBO LNS (55) (55) 1 772
Assets 142858 RE - SBO LOANS ON NONACCRUAL 90 90 1 774
Assets 142859 RE - SBO LOANS ON NONACCRUAL CONTRA (66) (66) 1 776
Assets 142860 SBA DISCOUNT ON RETAINED PORTION (77) (77) 1 778
Assets 142872 DEFERRED FEE-RESID CRA PURCH LNS-SBO (88) (88) 1 780
Assets 142874 UNAMORTIZED COSTS-RESIDE CRA PURCH LNS-SBO 100 100 1 782
Assets 143000 HE & OTHER CONSUMER LONAS - ACQ 110 110 1 45
Assets 143001 PREM DISC - HE & OTHER CONS LONAS - ACQ 120 120 1 -
Assets 143002 MTM VAL - HE & OTHER CONS LONAS - ACQ (99) (99) 1 2,358
Assets 143003 HEL 1st Liens HFI - MSP 130 130 1 3,172
Assets 143004 Unamtzd Fees Costs - HEL 1st Liens HFI - MSP 140 140 1 798


sbbt tab

141720 $1
142091 $12
142168 $23
142800 $34
143000 $45
195908 $56
210115 $67
210215 $78
218305 $89
221526 $100
221556 $111
221802 $122
221852 $133
221876 $144
221881 $155
221927 $166
221962 $177
221993 $188
222011 $199
222016 $210
222021 $221
222312 $232
222325 $243
222343 $254

Pete_UK,

Your formula works but returns # value error if the data has a blank value. How would I incorporate to watch out for #value in the return answer?

Thanks in advance

Do you mean that B202 or H202 could be blank or that B2 could be blank (or all three)?

Pete

it is col C of 4_DMQ Data where the g/l number is available on B202 thus no match when looking at countif between col c and b202