Spreadsheet: 456868d1461093411-excel-formula-nested-functions-max-if-and-multiple-criteria-issues-example
Sheet: MO

 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2015-12-31 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | ACP Principal Amount
 |  |  |  | cstchctrn createdate | CstChcTrn TransDate | CstChcTrn ACPAmount | CstChcTrn VoidDate | TrnTypName |  | CstChcTrnID | CstchcTrnAcpInstallmentNumber | CstChcACP OriginalData | CstChcACPInstallmentDataLong | CstChcAmount | [NAME] |  |  |  |  |  |  |  |  | LST PD DATE
 |  |  |  | 2014-12-17 11:33:00 | 2014-12-17 00:00:00 | 1846.71 | NULL | Unearned ACP Interest
 |  |  |  | 2014-12-17 11:33:00 | 2014-12-17 00:00:00 | 1900 | NULL | Unearned ACP Principal |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | This is what I thought would work since array max(if(works for one criteria I thought I would try two with the and function but that didn't work
 |  |  |  | 2015-01-26 12:55:00 | 2015-01-26 00:00:00 | 327.84 | NULL | ACP Installment Amount
 |  |  |  | 2015-01-26 12:55:00 | 2015-01-26 00:00:00 | 0.16 | NULL | ACP Interest Amount |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Answer should be
 |  |  |  | 2015-01-26 12:55:00 | 2015-01-26 00:00:00 | 312.23 | NULL | ACP Interest Amount |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2015-11-28 00:00:00
 |  |  |  | 2015-01-26 12:55:00 | 2015-01-26 00:00:00 | 15.61 | NULL | ACP Late Fee |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Last time transaction ACP Principal Amount was paid before 12/31/2015
 |  |  |  | 2015-01-26 12:55:00 | 2015-01-26 00:00:00 | 0.16 | NULL | ACP Partial Payment Amount
 |  |  |  | 2015-02-25 10:27:00 | 2015-02-25 00:00:00 | 312.07 | NULL | ACP Installment Amount
 |  |  |  | 2015-02-25 10:27:00 | 2015-02-25 00:00:00 | 255.61 | NULL | ACP Interest Amount
 |  |  |  | 2015-02-25 10:27:00 | 2015-02-25 00:00:00 | 56.46 | NULL | ACP Principal Amount
 |  |  |  | 2015-03-25 09:28:00 | 2015-03-25 00:00:00 | 312.23 | NULL | ACP Installment Amount
 |  |  |  | 2015-03-25 09:28:00 | 2015-03-25 00:00:00 | 220.44 | NULL | ACP Interest Amount
 |  |  |  | 2015-03-25 09:28:00 | 2015-03-25 00:00:00 | 91.79 | NULL | ACP Principal Amount
 |  |  |  | 2015-04-24 14:35:00 | 2015-04-24 00:00:00 | 312.23 | NULL | ACP Installment Amount
 |  |  |  | 2015-04-24 14:35:00 | 2015-04-24 00:00:00 | 224.43 | NULL | ACP Interest Amount
 |  |  |  | 2015-04-24 14:35:00 | 2015-04-24 00:00:00 | 87.8 | NULL | ACP Principal Amount
 |  |  |  | 2015-05-26 10:10:00 | 2015-05-26 00:00:00 | 99.39 | NULL | ACP Interest Amount
 |  |  |  | 2015-05-26 10:10:00 | 2015-05-26 00:00:00 | 15.61 | NULL | ACP Late Fee
 |  |  |  | 2015-05-26 10:10:00 | 2015-05-26 00:00:00 | 115 | NULL | ACP Partial Payment Amount
 |  |  |  | 2015-06-02 12:16:00 | 2015-06-02 00:00:00 | 212.84 | NULL | ACP Installment Amount
 |  |  |  | 2015-06-02 12:16:00 | 2015-06-02 00:00:00 | 177.75 | NULL | ACP Interest Amount
 |  |  |  | 2015-06-02 12:16:00 | 2015-06-02 00:00:00 | 35.09 | NULL | ACP Principal Amount
 |  |  |  | 2015-06-22 15:08:00 | 2015-06-22 00:00:00 | 125 | NULL | ACP Interest Amount
 |  |  |  | 2015-06-22 15:08:00 | 2015-06-22 00:00:00 | 125 | NULL | ACP Partial Payment Amount
 |  |  |  | 2015-07-01 16:27:00 | 2015-07-01 00:00:00 | 196.59 | NULL | ACP Installment Amount
 |  |  |  | 2015-07-01 16:27:00 | 2015-07-01 00:00:00 | 76.73 | NULL | ACP Interest Amount
 |  |  |  | 2015-07-01 16:27:00 | 2015-07-01 00:00:00 | 9.36 | NULL | ACP Late Fee
 |  |  |  | 2015-07-01 16:27:00 | 2015-07-01 00:00:00 | 3.41 | NULL | ACP Partial Payment Amount
 |  |  |  | 2015-07-01 16:27:00 | 2015-07-01 00:00:00 | 3.41 | NULL | ACP Principal Amount
 |  |  |  | 2015-07-01 16:27:00 | 2015-07-01 00:00:00 | 110.5 | NULL | ACP Principal Amount
 |  |  |  | 2015-08-03 16:11:00 | 2015-08-03 00:00:00 | 138.82 | NULL | ACP Interest Amount
 |  |  |  | 2015-08-03 16:11:00 | 2015-08-03 00:00:00 | 15.44 | NULL | ACP Late Fee
 |  |  |  | 2015-08-03 16:11:00 | 2015-08-03 00:00:00 | 154.26 | NULL | ACP Partial Payment Amount
 |  |  |  | 2015-08-08 10:43:00 | 2015-08-08 00:00:00 | 170 | NULL | ACP Installment Amount
 |  |  |  | 2015-08-08 10:43:00 | 2015-08-08 00:00:00 | 107.03 | NULL | ACP Interest Amount
 |  |  |  | 2015-08-08 10:43:00 | 2015-08-08 00:00:00 | 62.97 | NULL | ACP Principal Amount
 |  |  |  | 2015-09-01 12:15:00 | 2015-09-01 00:00:00 | 112.23 | NULL | ACP Interest Amount
 |  |  |  | 2015-09-01 12:15:00 | 2015-09-01 00:00:00 | 15.61 | NULL | ACP Late Fee
 |  |  |  | 2015-09-01 12:15:00 | 2015-09-01 00:00:00 | 127.84 | NULL | ACP Partial Payment Amount
 |  |  |  | 2015-10-03 10:54:00 | 2015-09-09 00:00:00 | 86.2 | NULL | Acct Stop Accrual Flag
 |  |  |  | 2015-10-03 10:54:00 | 2015-10-03 00:00:00 | 150 | NULL | ACP Interest Amount
 |  |  |  | 2015-10-03 10:54:00 | 2015-10-03 00:00:00 | 150 | NULL | ACP Partial Payment Amount
 |  |  |  | 2015-10-08 12:31:00 | 2015-10-08 00:00:00 | 50 | NULL | ACP Installment Amount
 |  |  |  | 2015-10-08 12:31:00 | 2015-10-08 00:00:00 | 14.39 | NULL | ACP Interest Amount
 |  |  |  | 2015-10-08 12:31:00 | 2015-10-08 00:00:00 | 50 | NULL | ACP Interest Amount
 |  |  |  | 2015-10-08 12:31:00 | 2015-10-08 00:00:00 | 15.61 | NULL | ACP Late Fee
 |  |  |  | 2015-10-08 12:31:00 | 2015-10-08 00:00:00 | 30 | NULL | ACP Partial Payment Amount
 |  |  |  | 2015-11-03 10:49:00 | 2015-11-03 00:00:00 | 160 | NULL | ACP Interest Amount
 |  |  |  | 2015-11-03 10:49:00 | 2015-11-03 00:00:00 | 160 | NULL | ACP Partial Payment Amount
 |  |  |  | 2015-11-04 10:55:00 | 2015-11-04 00:00:00 | 137.84 | NULL | ACP Installment Amount
 |  |  |  | 2015-11-04 10:55:00 | 2015-11-04 00:00:00 | 59.06 | NULL | ACP Interest Amount
 |  |  |  | 2015-11-04 10:55:00 | 2015-11-04 00:00:00 | 12.16 | NULL | ACP Late Fee
 |  |  |  | 2015-11-04 10:55:00 | 2015-11-04 00:00:00 | 12.16 | NULL | ACP Partial Late Fee
 |  |  |  | 2015-11-04 10:55:00 | 2015-11-04 00:00:00 | 12.16 | NULL | ACP Partial Payment Amount
 |  |  |  | 2015-11-04 10:55:00 | 2015-11-04 00:00:00 | 78.78 | NULL | ACP Principal Amount
 |  |  |  | 2015-11-28 15:26:00 | 2015-11-28 00:00:00 | 315.68 | NULL | ACP Installment Amount
 |  |  |  | 2015-11-28 15:26:00 | 2015-11-28 00:00:00 | 140.74 | NULL | ACP Interest Amount
 |  |  |  | 2015-11-28 15:26:00 | 2015-11-28 00:00:00 | 3.45 | NULL | ACP Late Fee
 |  |  |  | 2015-11-28 15:26:00 | 2015-11-28 00:00:00 | 15.61 | NULL | ACP Late Fee
 |  |  |  | 2015-11-28 15:26:00 | 2015-11-28 00:00:00 | 25.62 | NULL | ACP Partial Payment Amount
 |  |  |  | 2015-11-28 15:26:00 | 2015-11-28 00:00:00 | 10.01 | NULL | ACP Principal Amount
 |  |  |  | 2015-11-28 15:26:00 | 2015-11-28 00:00:00 | 171.49 | NULL | ACP Principal Amount
 |  |  |  | 2015-12-31 11:45:00 | 2015-12-31 00:00:00 | 302.22 | NULL | ACP Installment Amount
 |  |  |  | 2015-12-31 11:45:00 | 2015-12-31 00:00:00 | 61.07 | NULL | ACP Interest Amount
 |  |  |  | 2015-12-31 11:45:00 | 2015-12-31 00:00:00 | 22.78 | NULL | ACP Late Fee
 |  |  |  | 2015-12-31 11:45:00 | 2015-12-31 00:00:00 | 22.78 | NULL | ACP Partial Late Fee
 |  |  |  | 2015-12-31 11:45:00 | 2015-12-31 00:00:00 | 22.78 | NULL | ACP Partial Payment Amount
 |  |  |  | 2015-12-31 11:45:00 | 2015-12-31 00:00:00 | 241.15 | NULL | ACP Principal Amount
 |  |  |  | 2016-02-01 13:04:00 | 2016-02-01 00:00:00 | 24.74 | NULL | ACP Late Fee
 |  |  |  | 2016-02-01 13:04:00 | 2016-02-01 00:00:00 | 312 | NULL | ACP Partial Payment Amount
 |  |  |  | 2016-02-01 13:04:00 | 2016-02-01 00:00:00 | 287.26 | NULL | ACP Principal Amount
 |  |  |  | 2016-02-23 11:32:00 | 2016-02-23 00:00:00 | 312.18 | NULL | ACP Partial Payment Amount
 |  |  |  | 2016-02-23 11:32:00 | 2016-02-23 00:00:00 | 312.18 | NULL | ACP Principal Amount
 |  |  |  | 2016-03-02 12:27:00 | 2016-03-02 00:00:00 | 125 | NULL | ACP Partial Payment Amount
 |  |  |  | 2016-03-02 12:27:00 | 2016-03-02 00:00:00 | 125 | NULL | ACP Principal Amount
 |  |  |  | 2016-03-04 08:51:00 | 2016-03-04 00:00:00 | 226.11 | NULL | CstChcAmount Update Complete
 |  |  |  | 2016-03-04 18:27:00 | 2016-03-04 00:00:00 | 226.11 | NULL | TMS Loan Charged Off
 |  |  |  | 2016-03-04 18:27:00 | 2016-03-04 00:00:00 | 226.11 | NULL | TMS Loan Charged Off Receivable
 |  |  |  | 2016-03-04 18:27:00 | 2016-03-04 00:00:00 | 226.11 | NULL | TMS Loan Charged Off to Collections
 |  |  |  | 2016-04-06 14:38:00 | 2016-04-06 00:00:00 | 226.11 | NULL | [NAME]
 |  |  |  | 2016-04-06 14:38:00 | 2016-04-06 00:00:00 | 226.11 | NULL | TMS Collections Bad Debt Payment