SUMIF AND formula

To use the SUM and IF worksheet functions together to sum multiple criteria, refer to the following examples.

Back to the top

Example 1: Nested IF Function with Boolean OR (+)

1. Start Excel and create the following worksheet:

      A1: Dept  B1: Employees C1: Criteria
      A2: A     B2: 2	   C2: A
      A3: B     B3: 4	   C3: B
      A4: C     B4: 3		
      A5: A     B5: 3		
      A6: B     B6: 3		
      A7: C     B7: 2		
      A8: A     B8: 4		
      A9: C     B9: 3		
   					

2. In cell D1, type the following formula:
   =SUM(IF((A2:A9="A")+(A2:A9="B"),B2:B9,0))
3. Press CTRL+SHIFT+ENTER (or COMMAND+RETURN on the Mac) to enter the formula as an array formula.
   
   The formula returns 16, the total number of employees in departments A and B.

Back to the top

Example 2: Nested IF Function with Criteria Array ({"A","B"})

1. Repeat step 1 from Example 1.
2. In cell D2, type the following formula:
   =SUM(IF(A2:A9={"A","B"},B2:B9,0))
3. Press CTRL+SHIFT+ENTER (or COMMAND+RETURN on the Mac) to enter the formula as an array formula.
   
   The formula once again returns 16.

Example 3: SUMIF() Worksheet Function

1. Repeat step 1 from Example 1.
2. In cell D3, type the following formula:
   =SUMIF(A2:A9,C2:C3,B2:B9)
3. Press ENTER (or RETURN on the Mac) to enter the formula. The formula returns 9, the number of employees who meet criteria A.
4. Grab the fill handle and fill the formula down to cell D4.
    

i need to give two range i.e. name and units should be equal to differ criteria.

The above formula is not working in my sheet.

Please find attached file

Please give example and get command in excel here.

Pls help me-What command is used in excel 2003 to eliminate duplicate data.

how to use sumproduct formula

Excel is the most powerful tool to manage and analyze various types of Data. This Microsoft Excel tutorial for beginners covers in-depth lessons for Excel learning and how to use various Excel formulas, tables and charts for managing small to large scale business process.

This free Excel videos for beginners course will help you learn Excel basics.

Click to see Videos - Excel Formulas | Excel Full Tutorials