Midler Corporation operates in an industry that has a high rate of bad debts and uses the allowance method to account for its bad debt expenses. Before any year-adjustment on 7/31/2014, Midler's accountant compiled a list of account receivables, showing customer names, invoices amounts, invoice dates, and due dates as below

Customer	InvoiceAmount	InvoiceDate	Due Date	DaysLate
Dang, Trista	$343.00	11/8/2013	2/23/2014
Potts, Burma	$1,939.00	2/5/2014	3/23/2014
Caruso, Ninfa	$3,774.00	4/27/2014	6/9/2014
Mosier, Maude	$4,378.00	11/29/2013	2/3/2014
Casper, Senaida	$2,168.00	3/11/2014	5/13/2014
Lemieux, Kiana	$873.00	1/16/2014	2/17/2014
Cousins, Jeniffer	$6,013.00	11/28/2013	3/18/2014
Moe, Christi	$4,914.00	2/12/2014	3/5/2014
Rapp, Jeni	$245.00	4/9/2014	6/5/2014
Whitman, Donita	$311.00	3/18/2014	5/23/2014
Galindo, Retha	$1,251.00	1/21/2014	2/16/2014
Mcintosh, Janeth	$1,017.00	1/29/2014	2/26/2014
Brant, Elouise	$6,476.00	1/11/2014	3/4/2014
Oconnor, Nery	$2,006.00	3/29/2014	5/6/2014
Larsen, Kaila	$1,441.00	6/1/2014	7/28/2014
Brackett, Curtis	$4,850.00	2/27/2014	3/20/2014
Glynn, Louis	$2,995.00	3/27/2014	5/29/2014
Angulo, Dierdre	$6,260.00	3/30/2014	6/12/2014
Barger, Lorelei	$1,858.00	4/14/2014	7/2/2014
Byers, Natisha	$2,940.00	4/4/2014	5/7/2014
Whited, Un	$4,797.00	10/10/2013	12/5/2013
Easter, Valarie	$3,496.00	4/25/2014	6/27/2014
Tolbert, Yoshiko	$1,891.00	4/8/2014	4/30/2014
Newby, Kiersten	$1,011.00	3/14/2014	4/27/2014
Shank, Sebastian	$3,612.00	4/8/2014	5/14/2014
Saldana, Venita	$2,486.00	4/12/2014	6/30/2014
Ho, Sylvie	$4,292.00	11/4/2013	12/21/2013
Schubert, Gwenn	$6,497.00	3/9/2014	4/10/2014
Eldridge, Bridgett	$4,280.00	5/11/2014	7/13/2014
Adam, Elmer	$3,920.00	5/14/2014	6/21/2014
Allred, Elina	$2,846.00	4/16/2014	5/17/2014
Arnett, Helaine	$777.00	3/23/2014	5/18/2014
Pack, Bradly	$1,027.00	4/26/2014	6/18/2014
Hardison, Kacy	$3,452.00	4/22/2014	6/18/2014
Zook, Corrinne	$1,718.00	10/4/2013	1/9/2014
Pimentel, Cristopher	$1,290.00	11/30/2013	4/21/2014
Grooms, Otilia	$1,070.00	4/19/2014	5/14/2014
Buffington, Fonda	$1,184.00	5/28/2014	7/24/2014
Loftin, Burton	$6,383.00	5/30/2014	7/28/2014
Bray, Errol	$3,869.00	4/17/2014	6/7/2014
Toler, Evita	$5,022.00	5/17/2014	7/6/2014
Shah, Celsa	$1,411.00	1/2/2014	2/2/2014
Turk, Hildegarde	$3,851.00	5/7/2014	6/27/2014
Hummel, Joya	$3,030.00	4/7/2014	5/7/2014
Scanlon, Catherina	$3,458.00	3/3/2014	5/15/2014
Johns, Yuonne	$1,414.00	11/25/2013	1/30/2014
Clemmons, Delpha	$2,034.00	5/8/2014	7/16/2014
Heaton, Lin	$4,377.00	5/28/2014	7/5/2014
Muniz, Domitila	$4,272.00	5/29/2014	8/2/2014
Hickey, Melina	$3,531.00	11/3/2013	12/21/2013
Dupont, Mica	$125.00	1/30/2014	3/8/2014
Hindman, Dorthey	$2,647.00	4/25/2014	5/30/2014
Dumas, Genie	$4,559.00	3/1/2014	4/30/2014
Leary, Janelle	$5,027.00	3/17/2014	4/18/2014
Jeffery, Alaine	$6,372.00	4/7/2014	6/5/2014
Bittner, Kiyoko	$5,185.00	3/11/2014	5/25/2014
Marcus, Iona	$3,080.00	3/23/2014	4/30/2014
Beals, Wyatt	$5,987.00	4/1/2014	6/19/2014
Button, Berna	$4,582.00	2/23/2014	5/13/2014
Gillen, Andree	$4,033.00	5/13/2014	7/12/2014
Paz, Maia	$4,294.00	4/22/2014	6/14/2014
Mathias, Debrah	$1,416.00	10/10/2013	11/11/2013
Larose, Gwyneth	$3,815.00	3/2/2014	4/29/2014
Roach, Allena	$5,443.00	3/14/2014	5/25/2014
Carey, Kristan	$5,095.00	4/3/2014	5/30/2014
Hickson, Margareta	$2,972.00	5/18/2014	7/18/2014
Breeden, Karol	$2,095.00	3/23/2014	5/31/2014
Hancock, Richie	$4,841.00	5/31/2014	7/4/2014
Pride, Adolfo	$448.00	4/5/2014	6/10/2014
Boyles, Ilda	$5,741.00	4/12/2014	5/9/2014
Kinard, Laquita	$3,459.00	2/28/2014	3/22/2014
Ervin, Lavern	$1,025.00	4/2/2014	5/19/2014
Watt, Edward	$3,994.00	5/11/2014	6/24/2014
Fournier, Dorene	$4,471.00	4/30/2014	7/8/2014
Hook, Marisela	$6,475.00	3/4/2014	5/21/2014
Wofford, Reyes	$4,865.00	10/21/2013	3/11/2014
Pak, Dollie	$1,644.00	4/16/2014	6/19/2014
Torrence, Armida	$2,031.00	3/10/2014	5/8/2014
Eckert, Xiomara	$3,741.00	3/4/2014	5/3/2014
Minor, Ranee	$5,264.00	5/12/2014	6/23/2014
Hutchens, Maryanne	$1,651.00	5/23/2014	7/2/2014
Doughty, Teena	$3,457.00	4/10/2014	6/29/2014
Hanley, Leatrice	$2,038.00	10/31/2013	1/19/2014
Freedman, Kathlyn	$4,562.00	12/23/2013	1/24/2014
Grimes, Isis	$4,694.00	1/24/2014	3/28/2014
Perreault, Angelique	$2,402.00	1/6/2014	1/28/2014
Walter, Bernardina	$5,660.00	1/3/2014	3/22/2014
Parr, Consuela	$2,246.00	1/24/2014	3/26/2014
Reece, Maisie	$5,914.00	2/17/2014	5/8/2014
Harlow, Carmel	$5,513.00	2/28/2014	4/19/2014
Honeycutt, Lane	$4,806.00	3/15/2014	5/24/2014
Cass, Anthony	$2,015.00	5/4/2014	6/13/2014
Steffen, Malinda	$3,282.00	10/1/2013	2/23/2014
Mccrary, Merlyn	$4,569.00	1/26/2014	2/18/2014
Littleton, Izola	$2,220.00	3/1/2014	4/12/2014
Nieves, Treva	$4,303.00	4/17/2014	7/1/2014
Mclean, Eldora	$1,929.00	10/14/2013	3/10/2014
Dancy, Deena	$2,449.00	3/7/2014	4/12/2014
Anglin, Thanh	$1,617.00	10/21/2013	1/22/2014
Schrader, Numbers	$2,138.00	2/25/2014	3/30/2014
Edgar, Dusti	$5,261.00	11/5/2013	2/20/2014
Eldridge, Beckie	$2,932.00	2/27/2014	3/22/2014
Picard, Margherita	$4,596.00	5/26/2014	7/11/2014
Sander, Carin	$6,391.00	4/29/2014	6/14/2014
Bonilla, Michel	$5,394.00	12/31/2013	3/7/2014
Hartmann, Samual	$5,171.00	3/10/2014	4/14/2014
Dew, Kaye	$3,267.00	4/10/2014	6/27/2014
Fuchs, Anneliese	$523.00	3/27/2014	5/4/2014
Timmons, Devon	$3,480.00	4/24/2014	6/29/2014
Gallagher, Damian	$6,121.00	4/19/2014	5/10/2014
Coffin, Wai	$3,147.00	5/17/2014	7/21/2014
Wick, Tamatha	$3,094.00	1/26/2014	3/25/2014
Elston, Shara	$4,993.00	4/10/2014	6/18/2014
Fitzpatrick, Hunter	$5,603.00	4/29/2014	6/3/2014
Paquette, Cathey	$2,412.00	4/29/2014	6/8/2014
Mcreynolds, Fern	$1,641.00	3/24/2014	4/30/2014
Damico, Ammie	$4,335.00	6/1/2014	7/15/2014
Gallardo, Armida	$855.00	3/30/2014	5/27/2014
Lentz, Rosana	$1,822.00	10/29/2013	3/10/2014
Delossantos, Son	$61.00	5/16/2014	7/11/2014
Cortes, Petrina	$4,477.00	5/11/2014	7/10/2014
Settle, Madelene	$111.00	10/20/2013	1/8/2014
Hibbard, Marceline	$4,904.00	12/21/2013	5/9/2014
Seidel, Maren	$879.00	4/17/2014	5/23/2014
Oconner, Glynda	$3,469.00	4/22/2014	6/9/2014
Spooner, Maryln	$1,530.00	5/30/2014	8/4/2014
Briones, Adria	$499.00	4/23/2014	5/1/2014
Roden, Marylee	$5,077.00	5/30/2014	8/3/2014
Valentin, Regena	$5,153.00	1/17/2014	2/7/2014
Tillman, Yasmine	$4,718.00	5/9/2014	7/1/2014
Burrows, Merideth	$4,393.00	5/15/2014	6/27/2014
Shuman, Cordia	$5,681.00	12/7/2013	3/20/2014
Crump, Glinda	$2,257.00	5/5/2014	6/7/2014
Silverman, Delmar	$2,237.00	10/15/2013	1/30/2014
Gilman, Romona	$970.00	12/11/2013	1/17/2014
Kyle, Addie	$205.00	4/26/2014	7/4/2014
Epps, Eldora	$1,712.00	4/25/2014	6/10/2014
Smiley, Brunilda	$282.00	1/21/2014	2/14/2014
Pennell, Willena	$1,440.00	10/6/2013	1/26/2014
Shah, Lorenza	$2,656.00	5/23/2014	7/1/2014
Thigpen, Mia	$5,517.00	10/17/2013	1/17/2014
Patino, Joseph	$2,686.00	4/18/2014	5/31/2014
Coombs, Rasheeda	$4,241.00	12/14/2013	3/1/2014
Epstein, Lakia	$3,821.00	4/20/2014	5/31/2014
Dailey, Joel	$1,099.00	3/24/2014	5/8/2014
Blanchard, Rubi	$3,450.00	4/25/2014	7/1/2014
Nutt, Altagracia	$2,044.00	5/29/2014	7/22/2014
Haight, Darrick	$4,343.00	4/13/2014	5/13/2014
Hemphill, Monnie	$5,836.00	12/13/2013	2/13/2014
Parson, Terrilyn	$5,444.00	4/4/2014	5/16/2014
Mccall, Kasie	$2,633.00	4/23/2014	5/12/2014
Pollack, Latesha	$1,059.00	5/18/2014	7/5/2014
Rubin, Reynaldo	$6,493.00	4/17/2014	5/20/2014
Brill, Evelynn	$835.00	11/23/2013	3/15/2014
Irby, Tama	$3,846.00	11/2/2013	1/12/2014
Hatton, Faustina	$5,513.00	5/1/2014	5/31/2014
Finnegan, Marine	$1,025.00	3/29/2014	6/14/2014
Pierson, Effie	$1,927.00	3/5/2014	4/16/2014
Ashe, Beata	$2,743.00	4/15/2014	6/30/2014
Taft, Lala	$329.00	4/3/2014	6/16/2014
Rutledge, Apryl	$4,869.00	4/22/2014	6/2/2014
Byrnes, Edmundo	$2,476.00	2/27/2014	5/8/2014
Baca, Tressa	$175.00	10/6/2013	12/14/2013
Mckinley, Jonh	$584.00	3/21/2014	6/6/2014
Mckeown, Aisha	$3,846.00	2/9/2014	4/20/2014
Ezell, Scarlet	$5,955.00	3/6/2014	5/14/2014
Conners, Julissa	$2,780.00	2/2/2014	4/3/2014
Herrick, Jae	$4,013.00	1/21/2014	3/7/2014
Pereira, Otilia	$6,470.00	1/18/2014	2/9/2014
Hilliard, Dion	$4,254.00	3/15/2014	5/25/2014
Rayford, Karol	$5,857.00	6/1/2014	7/30/2014
Lavigne, Zetta	$5,594.00	3/16/2014	4/28/2014
Doty, Zoila	$3,366.00	1/6/2014	2/12/2014
Bedford, Michael	$137.00	4/23/2014	4/29/2014
Barden, Billye	$4,044.00	4/5/2014	5/8/2014
Stoddard, Sondra	$3,095.00	4/2/2014	6/16/2014
Varner, Alba	$5,636.00	1/12/2014	2/24/2014
Harlow, Gilda	$6,267.00	1/16/2014	3/25/2014
Vallejo, Ophelia	$867.00	3/19/2014	5/15/2014
Shields, Madie	$13.00	3/17/2014	5/2/2014
Mcconnell, Jonna	$1,499.00	1/5/2014	3/12/2014
Horn, Elissa	$1,575.00	5/18/2014	7/8/2014
Beebe, Leonila	$3,750.00	3/31/2014	6/17/2014
Snow, Tamera	$2,132.00	4/12/2014	5/15/2014
Creel, Jodee	$4,158.00	4/24/2014	5/31/2014
Joy, Vern	$5,658.00	5/8/2014	7/1/2014
Dorsey, Khalilah	$217.00	1/5/2014	1/26/2014
Meek, Exie	$4,107.00	3/28/2014	4/23/2014
Marvin, Maybell	$1,356.00	12/27/2013	2/28/2014
Mccain, Elba	$6,448.00	10/5/2013	11/16/2013
Breen, Dakota	$1,752.00	5/18/2014	6/18/2014
Benner, Valda	$1,956.00	3/4/2014	5/5/2014
Spalding, Elisha	$3,522.00	4/9/2014	6/4/2014
Lance, Mitsue	$1,704.00	1/21/2014	3/3/2014
Monahan, Tanika	$1,601.00	4/25/2014	5/28/2014
Gold, Theola	$715.00	1/5/2014	2/13/2014
Carlisle, Cameron	$4,416.00	3/31/2014	4/30/2014
Tobin, Dalila	$1,098.00	5/5/2014	7/4/2014
Clanton, Kendall	$2,508.00	2/17/2014	4/28/2014
Spann, Randi	$4,101.00	5/29/2014	7/1/2014

Days Account Late	Estimated Bad Debt Percentage	Count of Late Payments	Sum of Late Payments	Estimated Bad Debts
1~19 Days	20%
20-39 Days	40%
40-59 Days	60%
60~79 Days	80%
80-99 Days	90%
100-119 Days	96%
120-139 Days	98%
140+ Days	100%
Total

Determine the number and amount of total late payments. Use CountIFS, SumIFS, and other appropriate Excel functions to calculate Dayslate, count of late payment, sum of late payment, and estimated bad debts in order to complete the table above.

If you guys don't mind, show me the formula functions so I can have a better understanding when I use excel. Most of the time I struggle a lot figuring out how to put the formula)