I am trying to sum information if the my data meets three criteria: 1) customer is 98012, 2) Currency is USD, 3) Invoice starts with a specific letter. The last part is where I am having my problem. I am using the sum if formula, but I can't seem to isolate the invoice number. I tried: IF(+LEFT(E:E,1)="T",+SUMIFS(C:C,A:A,"98012",E:E,">T",B:B,"USD"),"") and that worked, but only because T invoices are the last alpha character. When I change it to O: IF(+LEFT(E:E,1)="O",+SUMIFS(C:C,A:A,"98012",E:E,">O",B:B,"USD"),"") it either doesn't work at all or it adds the O and the T invoices and that's not what I want. Plus, there must be something better than this if statement. So I want to be able to determine what is the total for customer 98012 if the currency code is USD and the Invoice starts with a T.
Can someone help?
Customer Currency Amount Date Invoice
98012 USD 72662.5 8-Mar-12 T114555
98012 USD 32539.52 30-Mar-12 O229580
98012 USD 29635.77 15-Mar-12 O214262
98012 USD 15018.24 30-Mar-12 T126466
98012 USD 7509.12 23-Mar-12 T122940
98012 EUR 6006 13-Mar-12 T116862
98012 USD 5006.08 27-Mar-12 T124332
98012 USD 2503.04 16-Mar-12 T119070
98012 USD 15518.2 10-Apr-12 T131756
98012 EUR 9702 31-May-12 O283985
94034 USD 8869.7 28-Mar-12 O214265
94034 EUR 162.83 28-Mar-12 B353149
94034 CHF 93.94 28-Mar-12 B353114
94034 USD 37411.49 23-May-12 T155800
94034 USD 32886.04 30-May-12 T158846
Bookmarks