Tanu (Accountant)     26 November 2010

# SUMIF AND formula

tell me how to use "sumif and" formula

Ram Avtar Singh (Nagari Sultanpur U.P.Delhi)     26 November 2010

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.

Tanu (Accountant)     27 November 2010

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

Tanu (Accountant)     27 November 2010

The above formula is not working in my sheet.

Ram Avtar Singh (Nagari Sultanpur U.P.Delhi)     27 November 2010

Please find attached file

Attached File : 22 sum command.xlsx downloaded: 126 times

Ram Avtar Singh (Nagari Sultanpur U.P.Delhi)     27 November 2010

Please give example and get command in excel here.

Varun.T.K (CA Final, CS Executive)     27 November 2010

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

Manoj Garg (Support on Tally Excel Password Recovery etc.)     27 November 2010

Find Duplicate in Excel - Addin

Attached File : 50 duplicates.zip downloaded: 177 times

Manoj Garg (Support on Tally Excel Password Recovery etc.)     27 November 2010

SUMIF can only be used in situations where the summing is based on a single condition. Use instead: (1) =SUMPRODUCT() or equivalently (2) =SUM((a*b)*(b*c)*(c*d)) The second is an array formula that must be entered by hitting CONTROL+SHIFT+ENTER at the same time (not just ENTER).

Tanu (Accountant)     27 November 2010

how to use sumproduct formula

Babita Jain (Personal Assistant)     10 May 2021

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

Your are not logged in . Please login to post replies

view more »