Excel - SUMIF
1-In the Employees worksheet, in column M use SUMIF to show Total Sales(r) (column O in OrderHeaders) for each employee ID. Note that each employee is associated with many orders. Also keep in mind absolute cell references.
2-SUMIFS - In the Employees worksheet, in column N use SUMIFS to show:
Total Sales (column O in OrderHeaders) for each employee but only include orders that have been shipped (column G in OrderHeaders) in this calclation. Note that an order has been shipped if ShippedDate is not empty, indicated using (note that there is nothing to the right of as blank in Excel is indicated by nothing.
I looking for the right statement on M2, N2 cells on the employee sheet. Please, Don't tell me there are missing values!. I am only looking for the right statements. ignore the data. Don't send syntax steps. I looking for the exact statement in each cell. Be careful, we have here, two sheets we need to use order headers sheet on the employee sheet at M2, N2 respectively. THX IN ADV.
N TotalSalesShipped A B D E F G 1 Employeeld LastName FirstName Title Birth Date HireDate Address 2 1 Davolio Nancy Sales Representative 6/21/1989 11/12/2012 507 - 20th Ave. E.Apt. 2A 3 2 Fuller Andrew Vice President, Sales 9/1/1972 2/25/2013 908 W. Capital Way 4 3 Leverling Janet Sales Representative 3/12/1984 10/13/2012 722 Moss Bay Blvd. 5 4 Peacock Margaret Sales Representative 4/2/1979 11/14/2013 4110 Old Redmond Rd. 6 5 Buchanan Steven Sales Manager 9/15/1975 4/30/2014 14 Garrett Hill 7 6 Suyama Michael Sales Representative 1/13/1984 4/30/2014 Coventry HouseMiner Rd. 8 7 King Robert Sales Representative 12/10/1980 7/16/2014 Edgeham HollowWinchester Way 9 8 Callahan Laura Inside Sales Coordinator 7/23/1978 9/16/2014 4726 - 11th Ave. N.E. 10 9 Dodsworth Anne Sales Representative 1/13/1990 5/29/2015 7 Houndstooth Rd. 11 H City Seattle Tacoma Kirkland Redmond London London London Seattle London J M PostalCode Country HomePhone Reports To TotalSales 98122 USA (206) 555-9857 2 98401 USA (206) 555-9482 98033 USA (206) 555-3412 2 98052 USA (206) 555-8122 2 SW1 8JR UK (71) 555-4848 2 EC2 7JR UK (71) 555-7773 5 RG1 9SP UK (71) 555-5598 5 98105 USA (206) 555-1189 2 WG2 7LT UK (71) 555-4444 5 "50739 B D E F . J 1 Customer Customer CompanyName Employeeld OrderDate RequiredDate ShippedDate Ship Via Freight ShipName 2 WILMK Wilman Kala 5 1/15/2017 2/12/2017 42762 3 32.38 Vins et alcools Chevalier 3 TRADH Tradio Hipermercados 6 1/16/2017 2/27/2017 1/21/2017 1 11.61 Toms Spezialitten 4 HANAR Hanari Carnes 4 1/19/2017 2/16/2017 1/23/2017 2 65.83 Hanari Carnes 5 VICTE Victuailles en stock 3 1/19/2017 2/16/2017 1/26/2017 1 41.34 Viotuailles en stock 6 SUPRD Suprmes dlices 4 1/20/2017 2/17/2017 1/22/2017 2 51.30 Suprmes dlices 7 HANAR Hanari Carnes 3 1/21/2017 2/4/2017 1/27/2017 2 58.17 Hanari Carnes 8 CHOPS Chop-suey Chinese 5 1/22/2017 2/19/2017 2/3/2017 2 22.98 Chop-suey Chinese 9 PICSU Richter Supermarkt 9 1/23/2017 2/20/2017 1/26/2017 3 148.33 Richter Supermarkt 10 WELLI Wellington Importadora 3 1/26/2017 2/23/2017 1/28/2017 2 13.97 Wellington Importadora 11 HILAA HILARION-Abastos 4 1/27/2017 2/24/2017 2/2/2017 3 81.91 HILARIN-Abastos 12 ERNSH Ernst Handel 1 1/28/2017 2/25/2017 2/3/2017 1 140.51 Ernst Handel 13 CENTC Centro comercial Moctez 4 1/29/2017 2/26/2017 2/5/2017 3 3.25 Centro comercial Moctezuma 14 OLDWC Old World Delicatessen 4 1/30/2017 2/27/2017 2/9/2017 1 55.09 Ottilies Kseladen 15 QUEDE Que Delcia 4 1/30/2017 2/27/2017 2/10/2017 2 3.05 Que Delcia 16 RATTC Rattlesnake Canyon Gro 8 2/2/2017 3/2/2017 2/5/2017 3 48.29 Rattlesnake Canyon Grocery 17 ERNSH Ernst Handel 9 2/3/2017 3/3/2017 2/11/2017 3 146.06 Ernst Handel 18 FOLKO Folk och f HB 6 2/4/2017 3/3/2017 2/11/2017 3 3.67 Folk och f HB 19 BLONP Blondel pre et fils 2 2/5/2017 3/5/2017 2/23/2017 1 55.28 Blondel pre et fils 20 WARTH Wartian Herkku 3 2/6/2017 3/20/2017 2/11/2017 3 25.73 Wartian Herkku 21 FRANK Frankenversand 4 2/9/2017 3/9/2017 2/17/2017 1 208.58 Frankenversand 22 GROSR GROSELLA-Restaurant 8 2/10/2017 3/10/2017 2/13/2017 3 66.29 GROSELLA-Restaurante 23 WHITC White Clover Markets 5 2/11/2017 2/25/2017 2/20/2017 1 4.56 White Clover Markets 24 WARTH Wartian Herkku 1 2/12/2017 3/12/2017 2/13/2017 1 136.54 Wartian Herkku 25 SPLIR Split Rail Beer & Ale 6 2/12/2017 3/12/2017 3/13/2017 2 4.54 Split Rail Beer & Ale 26 RATTC Rattlesnake Canyon Gro 6 2/13/2017 3/13/2017 2/17/2017 2 98.03 Rattlesnake Canyon Grocery 27 QUICK QUICK-Stop 3 2/16/2017 3/16/2017 2/23/2017 3 76.07 QUICK-Stop 28 VINET Vins et aloools Chevalier 6 2/17/2017 3/17/2017 2/27/2017 1 6.01 Vins et aloools Chevalier 29 MAGAA Magazzini Alimentari Riur 1 2/18/2017 3/18/2017 2/20/2017 1 26.93 Magazzini Alimentari Riuniti K L M N 0 ShipAddress Ship City ShipPostalCod. Ship Country Order Total 59 rue de l'Abbaye Reims 51100 France $794.38 Luisenstr. 48 Mnster 44087 Germany $1,875.01 Rua do Pao, 67 Rio de Janeiro 05454-876 Brazil $1,618.43 2, rue du Commerce Lyon 69004 France $695.40 Boulevard Tirou, 255 Charleroi B-6000 Belgium $3,649.20 Rua do Pao, 67 Rio de Janeiro 05454-876 Brazil $1,502.97 Hauptstr. 31 Bern 3012 Switzerland $579.60 Starenweg 5 Genve 1204 Switzerland $2,638.83 Rua do Mercado, 12 Resende 08737-363 Brazil $531.77 Carrera 22 con Ave. Carlos Soublette #8-35 San Cristbal '5022 Venezuela $1,201.81 Kirchgasse 6 Graz 8010 Austria $1,755.39 Sierras de Granada 9993 Mxico D.F. 05022 Mexico $104.05 Mehrheimerstr. 369 Kln Germany $1,559.74 Rua da Panificadora, 12 Rio de Janeiro 02389-673 Brazil $451.05 2817 Milton Dr. Albuquerque (87110 USA $632.29 Kirchgasse 6 Graz 8010 Austria $2,019.86 kergatan 24 Brcke S-844 67 Sweden $699.29 24. place Klber Strasbourg 67000 France $1,231.28 Torikatu 38 Oulu 90110 Finland $372.29 Berliner Platz 43 Mnchen 180805 Germany $3,745.18 5. Ave. Los Palos Grandes Caracas 1081 Venezuela $1,167.49 1029 - 12th Ave. S. Seattle 98124 USA $646.76 Torikatu 38 Oulu 90110 Finland $1,512.54 P.O. Box 555 Lander 82520 USA $52.54 2817 Milton Dr. Albuquerque (87110 USA $1,554.03 Taucherstrae 10 Cunewalde 01307 Germany $2,113.35 59 rue de l'Abbaye Reims 51100 France $544.61 Via Ludovico il Moro 22 Bergamo 24100 Italy $318.77 N TotalSalesShipped A B D E F G 1 Employeeld LastName FirstName Title Birth Date HireDate Address 2 1 Davolio Nancy Sales Representative 6/21/1989 11/12/2012 507 - 20th Ave. E.Apt. 2A 3 2 Fuller Andrew Vice President, Sales 9/1/1972 2/25/2013 908 W. Capital Way 4 3 Leverling Janet Sales Representative 3/12/1984 10/13/2012 722 Moss Bay Blvd. 5 4 Peacock Margaret Sales Representative 4/2/1979 11/14/2013 4110 Old Redmond Rd. 6 5 Buchanan Steven Sales Manager 9/15/1975 4/30/2014 14 Garrett Hill 7 6 Suyama Michael Sales Representative 1/13/1984 4/30/2014 Coventry HouseMiner Rd. 8 7 King Robert Sales Representative 12/10/1980 7/16/2014 Edgeham HollowWinchester Way 9 8 Callahan Laura Inside Sales Coordinator 7/23/1978 9/16/2014 4726 - 11th Ave. N.E. 10 9 Dodsworth Anne Sales Representative 1/13/1990 5/29/2015 7 Houndstooth Rd. 11 H City Seattle Tacoma Kirkland Redmond London London London Seattle London J M PostalCode Country HomePhone Reports To TotalSales 98122 USA (206) 555-9857 2 98401 USA (206) 555-9482 98033 USA (206) 555-3412 2 98052 USA (206) 555-8122 2 SW1 8JR UK (71) 555-4848 2 EC2 7JR UK (71) 555-7773 5 RG1 9SP UK (71) 555-5598 5 98105 USA (206) 555-1189 2 WG2 7LT UK (71) 555-4444 5 "50739 B D E F . J 1 Customer Customer CompanyName Employeeld OrderDate RequiredDate ShippedDate Ship Via Freight ShipName 2 WILMK Wilman Kala 5 1/15/2017 2/12/2017 42762 3 32.38 Vins et alcools Chevalier 3 TRADH Tradio Hipermercados 6 1/16/2017 2/27/2017 1/21/2017 1 11.61 Toms Spezialitten 4 HANAR Hanari Carnes 4 1/19/2017 2/16/2017 1/23/2017 2 65.83 Hanari Carnes 5 VICTE Victuailles en stock 3 1/19/2017 2/16/2017 1/26/2017 1 41.34 Viotuailles en stock 6 SUPRD Suprmes dlices 4 1/20/2017 2/17/2017 1/22/2017 2 51.30 Suprmes dlices 7 HANAR Hanari Carnes 3 1/21/2017 2/4/2017 1/27/2017 2 58.17 Hanari Carnes 8 CHOPS Chop-suey Chinese 5 1/22/2017 2/19/2017 2/3/2017 2 22.98 Chop-suey Chinese 9 PICSU Richter Supermarkt 9 1/23/2017 2/20/2017 1/26/2017 3 148.33 Richter Supermarkt 10 WELLI Wellington Importadora 3 1/26/2017 2/23/2017 1/28/2017 2 13.97 Wellington Importadora 11 HILAA HILARION-Abastos 4 1/27/2017 2/24/2017 2/2/2017 3 81.91 HILARIN-Abastos 12 ERNSH Ernst Handel 1 1/28/2017 2/25/2017 2/3/2017 1 140.51 Ernst Handel 13 CENTC Centro comercial Moctez 4 1/29/2017 2/26/2017 2/5/2017 3 3.25 Centro comercial Moctezuma 14 OLDWC Old World Delicatessen 4 1/30/2017 2/27/2017 2/9/2017 1 55.09 Ottilies Kseladen 15 QUEDE Que Delcia 4 1/30/2017 2/27/2017 2/10/2017 2 3.05 Que Delcia 16 RATTC Rattlesnake Canyon Gro 8 2/2/2017 3/2/2017 2/5/2017 3 48.29 Rattlesnake Canyon Grocery 17 ERNSH Ernst Handel 9 2/3/2017 3/3/2017 2/11/2017 3 146.06 Ernst Handel 18 FOLKO Folk och f HB 6 2/4/2017 3/3/2017 2/11/2017 3 3.67 Folk och f HB 19 BLONP Blondel pre et fils 2 2/5/2017 3/5/2017 2/23/2017 1 55.28 Blondel pre et fils 20 WARTH Wartian Herkku 3 2/6/2017 3/20/2017 2/11/2017 3 25.73 Wartian Herkku 21 FRANK Frankenversand 4 2/9/2017 3/9/2017 2/17/2017 1 208.58 Frankenversand 22 GROSR GROSELLA-Restaurant 8 2/10/2017 3/10/2017 2/13/2017 3 66.29 GROSELLA-Restaurante 23 WHITC White Clover Markets 5 2/11/2017 2/25/2017 2/20/2017 1 4.56 White Clover Markets 24 WARTH Wartian Herkku 1 2/12/2017 3/12/2017 2/13/2017 1 136.54 Wartian Herkku 25 SPLIR Split Rail Beer & Ale 6 2/12/2017 3/12/2017 3/13/2017 2 4.54 Split Rail Beer & Ale 26 RATTC Rattlesnake Canyon Gro 6 2/13/2017 3/13/2017 2/17/2017 2 98.03 Rattlesnake Canyon Grocery 27 QUICK QUICK-Stop 3 2/16/2017 3/16/2017 2/23/2017 3 76.07 QUICK-Stop 28 VINET Vins et aloools Chevalier 6 2/17/2017 3/17/2017 2/27/2017 1 6.01 Vins et aloools Chevalier 29 MAGAA Magazzini Alimentari Riur 1 2/18/2017 3/18/2017 2/20/2017 1 26.93 Magazzini Alimentari Riuniti K L M N 0 ShipAddress Ship City ShipPostalCod. Ship Country Order Total 59 rue de l'Abbaye Reims 51100 France $794.38 Luisenstr. 48 Mnster 44087 Germany $1,875.01 Rua do Pao, 67 Rio de Janeiro 05454-876 Brazil $1,618.43 2, rue du Commerce Lyon 69004 France $695.40 Boulevard Tirou, 255 Charleroi B-6000 Belgium $3,649.20 Rua do Pao, 67 Rio de Janeiro 05454-876 Brazil $1,502.97 Hauptstr. 31 Bern 3012 Switzerland $579.60 Starenweg 5 Genve 1204 Switzerland $2,638.83 Rua do Mercado, 12 Resende 08737-363 Brazil $531.77 Carrera 22 con Ave. Carlos Soublette #8-35 San Cristbal '5022 Venezuela $1,201.81 Kirchgasse 6 Graz 8010 Austria $1,755.39 Sierras de Granada 9993 Mxico D.F. 05022 Mexico $104.05 Mehrheimerstr. 369 Kln Germany $1,559.74 Rua da Panificadora, 12 Rio de Janeiro 02389-673 Brazil $451.05 2817 Milton Dr. Albuquerque (87110 USA $632.29 Kirchgasse 6 Graz 8010 Austria $2,019.86 kergatan 24 Brcke S-844 67 Sweden $699.29 24. place Klber Strasbourg 67000 France $1,231.28 Torikatu 38 Oulu 90110 Finland $372.29 Berliner Platz 43 Mnchen 180805 Germany $3,745.18 5. Ave. Los Palos Grandes Caracas 1081 Venezuela $1,167.49 1029 - 12th Ave. S. Seattle 98124 USA $646.76 Torikatu 38 Oulu 90110 Finland $1,512.54 P.O. Box 555 Lander 82520 USA $52.54 2817 Milton Dr. Albuquerque (87110 USA $1,554.03 Taucherstrae 10 Cunewalde 01307 Germany $2,113.35 59 rue de l'Abbaye Reims 51100 France $544.61 Via Ludovico il Moro 22 Bergamo 24100 Italy $318.77
