Avail 20% discount on updated CA lectures for Dec 21 .Use Code RESULT20 !! Call : 088803-20003

ICICI

Share on Facebook

Share on Twitter

Share on LinkedIn

Share on Email

Share More

Tanu (Accountant)     26 November 2010

SUMIF AND formula

tell me how to use "sumif and" formula



 10 Replies

Ram Avtar Singh

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 (+)

loadTOCNode(2, 'moreinformation');
  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"})

loadTOCNode(2, 'moreinformation');
  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

loadTOCNode(2, 'moreinformation');
  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

Tanu (Accountant)     27 November 2010

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

Tanu

Tanu (Accountant)     27 November 2010

The above formula is not working in my sheet.

Ram Avtar Singh

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

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

Please give example and get command in excel here.

Varun.T.K

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

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

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

Tanu (Accountant)     27 November 2010

how to use sumproduct formula

Babita Jain

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


Leave a reply

Your are not logged in . Please login to post replies

Click here to Login / Register  


Start a New Discussion

Popular Discussion


view more »







Subscribe to the latest topics :
Search Forum:

Trending Tags