Go Ad-Free on CAclubindia with Daily Mobile Updates. Subscribe Now!

SUMIF AND formula

Tanu (Accountant) (767 Points)

26 November 2010

tell me how to use "sumif and" formula

Reply
Follow
Share

10 Replies

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

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

loadTOCNode(2, 'moreinformation');

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

In cell D1, type the following formula:
=SUM(IF((A2:A9="A")+(A2:A9="B"),B2:B9,0))
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"})

loadTOCNode(2, 'moreinformation');

Repeat step 1 from Example 1.
In cell D2, type the following formula:
=SUM(IF(A2:A9={"A","B"},B2:B9,0))
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');

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

Upgrade to CAclubindia Pro. Subscribe Now!

Tanu (Accountant) (767 Points)
Replied 27 November 2010

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

Tanu (Accountant) (767 Points)
Replied 27 November 2010

The above formula is not working in my sheet.

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

Please find attached file

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

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

Please give example and get command in excel here.

Upgrade to CAclubindia Pro. Subscribe Now!

Varun.T.K (CA Final, CS Executive) (365 Points)
Replied 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.) (1987 Points)
Replied 27 November 2010

Find Duplicate in Excel - Addin

Attached File : 50 duplicates.zip downloaded: 290 times

Manoj Garg (Support on Tally Excel Password Recovery etc.) (1987 Points)
Replied 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) (767 Points)
Replied 27 November 2010

how to use sumproduct formula

Babita Jain (Personal Assistant) (298 Points)
Replied 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