80.2K

Verified Solution

Link Copied!

Using sumif what equation would I use to solve these problems on excel.

File Home Insert Page Layout Formulas Data Review View 124 X for Utilities: Heating and cooling Nonmanufact MOH MOH MOH MOH MOH Account Amount Equipment de precistion: Molding $ 400,000 Equipment depreciation: Fabrication $ 300,000 Equipment depreciation Assembly $ 50,000 Equipment depreciation Material Handling $ 15.000 Equipment depreciation: Other $ 10,000 Production supricion Molding $ 60,000 Production pupervision: Fabrication $ 65,000 Production supervision: Ambly $ 58,000 Indirect labor: Maintenance & Repairs $ 72.000 Indirect Isbor: Material Handling $ 68,000 Indirect labor: Engineering $ 170,000 Indirect labor: Raw Materials Purchasing $ 33,000 Indirect labor: Quality Control $ 38.000 Indirect labor: Packaging & Shipping $ 33,000 Indirect materiale: Molding $ 18,000 Indirect materiale: Fabrication $ 13,000 Indirect materials: Ambly 8.000 Uti: Molding $ 24,000 1 Utilities: Fabrication $ 20.000 Utilities: Assembly $ 6,000 Utilik Plant lighting and other count $ 12,000 Utilities: Hesting and cooling 3 24.000 ES Property in reronce $ 26.000 Liability insurance $ 18.000 27 Medical insurance $ 45.000 at Workers' compensuon insurance $ 13.000 Other salaries: Plant manager $ 165.000 Other sories Accounting $ 89,000 1 Other guarie: Administrative support 1 56,000 32 Other larice Janitorial $ 48,000 33 Other Security and grounds maintenance ! 44,000 34 Property taxe $ 18,000 > Payroll taxe Direct labor $ 13,000 36 Payroll taxes Other $ 165,000 37 Direct materiale 3 2,220,000 34 Direct labor $ 1,365,000 34 Accounting misger's salary $ 88.000 40 Administrative office once $ 2,000 41 Advertising $ 400,000 02 Depreciation on office equipment $ 15,000 Hest & dectricity for the goal office $ 4,000 44 Janitorial services for administrative offices $ 26,000 Salaries of accounts rechbleible clerks + 78,000 45 Sales commission $ 480.000 Skenangay $ 120,000 4 Supplies for ministrative office $ 1000 Customer billig upplies $ 2,000 Product/ Period Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Period Period Period Period Period Period Period Period Period Period Period MOH MOH MOH MOH MOH MOH MOM MOH MOH MOH MOH MOH MOH MOH MOH MOH MOH MOH MOH MOH Direct materials Direct labor Administrative Administrative Selling Administrative Administrative Administrative Administrative Selling Selling Administrative Administrative Fixed ? Variable Fixed Fixed Fixed Fixed Fixed Fred Fixed Fired Fixed Fired Fored Fixed Fixed Fired Variable Variable Variable Variable Variable Variable Fixed Fixed Fixed Feed Fixed Fixed Fixed Feed Fixed Fixed Fixed Fired Variable Fixed Variable Variable Fixed Fixed Fixed Fixed Fixed Fixed Fored Variable Fired Fixed Vatible Pivot Table Charts Sumi Cost Summary Income Statements Type here to search File Home Insert Page Layout Formulas Data Review View F7 X for 1 Crispin Company 2 Traditional Format Income Statement 3 4 Sales $ 8,000,000 5 Cost of goods sold 6 Gross margin S 8,000,000 7 Selling and administrative expenses: 8 Selling 9 Administrative S 10 Net operating income $ 8,000,000 11 12 13 Crispin Company 14 Contribution Format Income Statement 15 Sales $ 8,000,000 16 Variable expenses: Cost of goods sold 18 Variable selling 19 Variable administrative $ 20 Contribution margin $ 8,000,000 21 Fixed expenses: 22 Fixed manufacturnig overhead Fixed selling 24 Fixed administrative 5 25 Net operating income S 8.000.000 26 27 28 Fill in 29 Auto-Calc 30 31 32 33 Pivot Table Charts Sumi Cost Summary Read 17 23 Inerme Stateme SumlF Function: SumlF is a function (formula) within Microsoft Excel that will add together cells within a range of defined cells that meet a specified criteria. This can be used if you want to add up all sales for a specific state. Below is a description of the Excel formula and an example of how to use it: 1.The SUMIF function has the following arguments: a.ESUMIF(range, criteria, sum_range) where: 1. The "range" is the selection of data (cells) that contains the criteria that you are interested in meeting. These values should correspond (be in the same row) as the data you are interested in summing 2. The "criteria" is the specific logic or value that you want to be met in the "range" in order to consider summing the corresponding value a)If this is a number you can just enter the number b)If this is text you must put it in quotations C)You can also reference another cell such as B5 here to be the result to be shown if true 1.The "sum_range" is the range data (cells) that correspond with the "range" that you want to total (sum) if they meet the criteria set forth in "criteria 1. The following is an example of a situation and the SumlF function. Using the below table, you want to sum the "Values" only where "VA" is the "State": a.In Cell B6 you would enter in the following formula: 1.=SUMIF(A2:A5,"VA",B2:B5) a)The "range" is the list of states (A2:A5) b)The "criteria" is "VA" indicating that we only want to sum the items in the "sum_range" if they equal "VA" c)The "sum_range" is the values (B2:35) corresponding to each state in each row of the "range" File Home Insert Page Layout Formulas Data Review View 124 X for Utilities: Heating and cooling Nonmanufact MOH MOH MOH MOH MOH Account Amount Equipment de precistion: Molding $ 400,000 Equipment depreciation: Fabrication $ 300,000 Equipment depreciation Assembly $ 50,000 Equipment depreciation Material Handling $ 15.000 Equipment depreciation: Other $ 10,000 Production supricion Molding $ 60,000 Production pupervision: Fabrication $ 65,000 Production supervision: Ambly $ 58,000 Indirect labor: Maintenance & Repairs $ 72.000 Indirect Isbor: Material Handling $ 68,000 Indirect labor: Engineering $ 170,000 Indirect labor: Raw Materials Purchasing $ 33,000 Indirect labor: Quality Control $ 38.000 Indirect labor: Packaging & Shipping $ 33,000 Indirect materiale: Molding $ 18,000 Indirect materiale: Fabrication $ 13,000 Indirect materials: Ambly 8.000 Uti: Molding $ 24,000 1 Utilities: Fabrication $ 20.000 Utilities: Assembly $ 6,000 Utilik Plant lighting and other count $ 12,000 Utilities: Hesting and cooling 3 24.000 ES Property in reronce $ 26.000 Liability insurance $ 18.000 27 Medical insurance $ 45.000 at Workers' compensuon insurance $ 13.000 Other salaries: Plant manager $ 165.000 Other sories Accounting $ 89,000 1 Other guarie: Administrative support 1 56,000 32 Other larice Janitorial $ 48,000 33 Other Security and grounds maintenance ! 44,000 34 Property taxe $ 18,000 > Payroll taxe Direct labor $ 13,000 36 Payroll taxes Other $ 165,000 37 Direct materiale 3 2,220,000 34 Direct labor $ 1,365,000 34 Accounting misger's salary $ 88.000 40 Administrative office once $ 2,000 41 Advertising $ 400,000 02 Depreciation on office equipment $ 15,000 Hest & dectricity for the goal office $ 4,000 44 Janitorial services for administrative offices $ 26,000 Salaries of accounts rechbleible clerks + 78,000 45 Sales commission $ 480.000 Skenangay $ 120,000 4 Supplies for ministrative office $ 1000 Customer billig upplies $ 2,000 Product/ Period Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Period Period Period Period Period Period Period Period Period Period Period MOH MOH MOH MOH MOH MOH MOM MOH MOH MOH MOH MOH MOH MOH MOH MOH MOH MOH MOH MOH Direct materials Direct labor Administrative Administrative Selling Administrative Administrative Administrative Administrative Selling Selling Administrative Administrative Fixed ? Variable Fixed Fixed Fixed Fixed Fixed Fred Fixed Fired Fixed Fired Fored Fixed Fixed Fired Variable Variable Variable Variable Variable Variable Fixed Fixed Fixed Feed Fixed Fixed Fixed Feed Fixed Fixed Fixed Fired Variable Fixed Variable Variable Fixed Fixed Fixed Fixed Fixed Fixed Fored Variable Fired Fixed Vatible Pivot Table Charts Sumi Cost Summary Income Statements Type here to search File Home Insert Page Layout Formulas Data Review View F7 X for 1 Crispin Company 2 Traditional Format Income Statement 3 4 Sales $ 8,000,000 5 Cost of goods sold 6 Gross margin S 8,000,000 7 Selling and administrative expenses: 8 Selling 9 Administrative S 10 Net operating income $ 8,000,000 11 12 13 Crispin Company 14 Contribution Format Income Statement 15 Sales $ 8,000,000 16 Variable expenses: Cost of goods sold 18 Variable selling 19 Variable administrative $ 20 Contribution margin $ 8,000,000 21 Fixed expenses: 22 Fixed manufacturnig overhead Fixed selling 24 Fixed administrative 5 25 Net operating income S 8.000.000 26 27 28 Fill in 29 Auto-Calc 30 31 32 33 Pivot Table Charts Sumi Cost Summary Read 17 23 Inerme Stateme SumlF Function: SumlF is a function (formula) within Microsoft Excel that will add together cells within a range of defined cells that meet a specified criteria. This can be used if you want to add up all sales for a specific state. Below is a description of the Excel formula and an example of how to use it: 1.The SUMIF function has the following arguments: a.ESUMIF(range, criteria, sum_range) where: 1. The "range" is the selection of data (cells) that contains the criteria that you are interested in meeting. These values should correspond (be in the same row) as the data you are interested in summing 2. The "criteria" is the specific logic or value that you want to be met in the "range" in order to consider summing the corresponding value a)If this is a number you can just enter the number b)If this is text you must put it in quotations C)You can also reference another cell such as B5 here to be the result to be shown if true 1.The "sum_range" is the range data (cells) that correspond with the "range" that you want to total (sum) if they meet the criteria set forth in "criteria 1. The following is an example of a situation and the SumlF function. Using the below table, you want to sum the "Values" only where "VA" is the "State": a.In Cell B6 you would enter in the following formula: 1.=SUMIF(A2:A5,"VA",B2:B5) a)The "range" is the list of states (A2:A5) b)The "criteria" is "VA" indicating that we only want to sum the items in the "sum_range" if they equal "VA" c)The "sum_range" is the values (B2:35) corresponding to each state in each row of the "range

Answer & Explanation Solved by verified expert

See Answer