Help and Support

Article ID: 275165 - Last Review: November 28, 2007 - Revision: 4.2

When to use a SUM(IF()) array formula

This article was previously published under Q275165

On This Page

Expand all | Collapse all

SUMMARY

You can use the SUMIF() worksheet function in Microsoft Excel for either of the following situations:
  • When you want to return a sum for a single criteria (for example, a single IF condition)
  • When you want to use multiple criteria and return the sum to multiple cells
The criteria that you can use with the SUMIF() worksheet function is limited to text, numbers, or a range, and the function cannot use array constants.

For additional information about array constants and worksheet functions, click the following article numbers to view the articles in the Microsoft Knowledge Base:
214286  (http://support.microsoft.com/kb/214286/ ) Some worksheet functions do not allow array constants
192222  (http://support.microsoft.com/kb/192222/ ) Some worksheet functions do not allow array constants
You cannot use the SUMIF() worksheet function with logical Boolean operators, such as OR and AND.

For additional information about worksheet functions and Boolean operators, click the following article number to view the article in the Microsoft Knowledge Base:
267982  (http://support.microsoft.com/kb/267982/ ) How to use a logical AND or OR in a SUM+IF statement in Excel
When you want to use multiple conditions, array constants, nested IF statements or Boolean operators; or you want one total for multiple conditions (criteria); use the two functions SUM and IF together. For example, use SUM(IF()) instead of the SUMIF() function.

This article demonstrates how to use the two functions SUM and IF together instead of the SUMIF() worksheet function.

MORE INFORMATION

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

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.

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.

    The formula returns 7, the number of employees who meet criteria B.

REFERENCES

For more information about the SUM worksheet function, click Microsoft Excel Help on the Help menu, type sum in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

For more information about the IF worksheet function, click Microsoft Excel Help on the Help menu, type if worksheet function in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

For more information about the SUMIF, click Microsoft Excel Help on the Help menu, type sumif in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

APPLIES TO
  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 97 Standard Edition
  • Microsoft Excel 2004 for Mac
  • Microsoft Excel X for Mac
  • Microsoft Excel 2001 for Mac
  • Microsoft Excel 98 for Macintosh
Keywords: 
kbhowto KB275165

Article Translations