An Introduction to Management Science 13th Edition by David Anderson,Dennis Sweeney ,Thomas Williams ,Jeffrey Camm, Kipp Martin

Exercise 9

Newton Manufacturing produces scientific calculators. The models are N350, N450, and the N900. Newton has planned its distribution of these products around eight customer zones: Brazil, China, France, Malaysia, U.S. Northeast, U.S. Southeast, U.S. Midwest, and U.S. West, Data for the current quarter (volume to be shipped in thousands of units) for each product and each customer zone are given in the file Newton_data.
Newton would like to know the total number of units going to each customer zone and also the total units of each product shipped. There are several ways to get this information from the data set. One way is to use the SUMIF function.
The SUMIF function extends the SUM function by allowing the user to add the values of cells meeting a logical condition. This general form of the function is
The test range is an area 10 search to test the condition , and the range to be summed is the position of the data to be summed. So, for example, using the Newton_data file, we would use the following function to get the total units sent to Malaysia:
Here, A3 is Malaysia, A3:A26 is the range of customer zones, and C3:C26 are the volumes for each product for these customer zones. The SUMIF looks for matches of Malaysia in column A and, if a match is found, adds the volume to the total. Use the SUMIF function to get each total volume by zone and each total volume by product.