Question

This table reports sales for each order.

                  In Order ID, the first 2 number is product code. The next 2 letters is location code.

For example in this order ID: 10-CA-2014-AB10015140-41954 reports transaction with product code 10 in location code CA

                  Find the total sales for all transactions related to location code AO. (Can you show excel work)

Order ID	Sale
10-CA-2014-AB10015140-41954	40098
10-IN-2014-JR162107-41675	26341
10-IN-2014-CR127307-41929	25330
10-ES-2014-KM1637548-41667	13524
10-SG-2014-RH9495111-41948	47221
10-IN-2014-JM156557-41818	22732
10-IN-2012-TS2134092-41219	30570
10-IN-2013-MB1808592-41378	31192
10-CA-2014-AB10015140-41954	40099
10-CA-2012-AB10015140-40974	36258
10-CA-2012-AB10015140-40974	36259
10-ID-2013-AJ107801-41383	28879
10-SA-2012-MM7260110-41269	45794
10-MX-2013-VF2171518-41591	4132
10-IN-2014-PF1912027-41796	27704
10-ES-2015-BP1118545-42216	13779
10-CA-2012-AB10015140-40958	39519
10-ES-2015-PJ1883564-42255	12069
10-IN-2015-JS156857-42035	22096
10-TZ-2015-RH9555129-42343	49463
10-PL-2013-AB600103-41494	46630
10-CA-2012-AB10015140-40974	36260
10-IN-2012-JK1532527-41030	21586
10-ES-2014-LB16795139-41697	13528
10-US-2015-NP1832582-42216	1570
10-MX-2015-VD2167039-42252	3484
10-IN-2012-PB19210127-41259	30191
10-ES-2012-EB1411048-40981	11645
10-CA-2012-AH10030140-41020	38460
10-IN-2013-BP1123058-41329	22999
10-US-2012-RR1952536-41270	220
10-ES-2013-BS1136545-41472	10648
10-CA-2013-AH10030140-41635	40977
10-IN-2012-DP131057-41215	21286
10-CA-2013-AH10030140-41635	40976
10-CG-2012-AH1003033-41165	47905
10-CA-2012-AH10030140-41041	36651
10-CA-2012-AH10030140-41273	40100
10-ES-2015-PO18865139-42018	15380
10-IN-2012-DL128657-40918	28046
10-ID-2014-JB1600059-41873	21316
10-IN-2015-BF1100558-42319	29272
10-IN-2015-VG2180558-42273	25795
10-ES-2013-GT14710139-41621	16681
10-IT-2012-ZC2191048-41174	15953
10-CA-2014-AH10030140-41719	34608
10-IT-2014-EB1384045-41706	13847
10-IN-2015-AP1091527-42244	24341
10-IN-2015-SW2027558-42125	28701
10-MX-2015-JH15820141-42356	6550
10-US-2012-AH10030140-41206	37425
10-IR-2015-TG1164060-42265	48360
10-CA-2012-AH10030140-41273	40101
10-IN-2012-PB19210127-41259	30190
10-MZ-2014-DG330087-41991	42336
10-IN-2013-SG2047058-41424	29047
10-CA-2012-AH10030140-41020	38461
10-IN-2013-GP1474058-41419	29601
10-IN-2015-MW182207-42221	23499
10-CA-2012-AH10030140-41041	36652
10-IN-2015-CS1246011-42074	26634
10-CA-2015-AH10030140-42357	34496
10-ES-2015-MS1798048-42227	10522
10-IT-2014-KC1667548-41882	10549
10-IN-2015-DB1340527-42022	25314
10-CA-2015-AS10045140-42007	32637
10-IT-2015-DK13090120-42345	16653
10-IN-2015-CS1184558-42354	28932
10-MX-2015-EM1420082-42328	8029
10-UP-2012-DW3480137-41221	50411
10-IN-2014-AS1022527-41797	21191
10-MX-2012-ST2053093-41234	4960
10-CG-2014-BW106533-41727	49085
10-IN-2015-LA1678059-42242	21209
10-ES-2013-AB10150139-41537	12161
10-IN-2015-JH158207-42140	25438
10-CA-2015-AS10045140-42218	33130
10-ES-2014-BE1141048-41967	16988
10-IN-2012-SZ2003527-41221	24443
10-IN-2012-RM1937558-41209	30199
10-ES-2015-HG1484545-42327	13879
10-MO-2015-DP310586-42305	50788
10-CA-2014-AS10045140-41908	40834
10-US-2015-AS10045140-42252	40425
10-ES-2014-JB1600048-41668	18241
10-ID-2013-AM1070559-41535	23013
10-MO-2014-AB25586-41966	45616
10-IN-2013-RP192707-41438	25026
10-IN-2014-BK1126011-41940	27962
10-IN-2014-JM1525027-41985	26669
10-IN-2013-GP1474058-41419	29600
10-CA-2015-AS10045140-42281	34336
10-ES-2014-NZ1856545-41975	19195
10-CA-2015-AS10045140-42007	32639
10-ID-2014-SC20695102-41667	26731
10-IN-2013-NC186257-41401	31278
10-ES-2014-NF183858-41970	12035
10-IN-2013-NZ185657-41606	29149
10-IT-2013-PM1894045-41546	15896

Answer 1

You can use ISNUMBER and SEARCH functions as shown below:

If "AO" is part of the string in cell C then the cell in column E will show TRUE else FALSE. Then you can use IF function in column F to tabulate that sales should be taken if "AO" is part of the string or 0. Then column F can be summed up to find total sales.

You can also combine the 2 statements into 1, as follows:

=IF(ISNUMBER(SEARCH("AO",C229))=TRUE,D229,0)

There is no location code "AO" in the given data, so total sales is zero.