Sum Multiple columns based on Criteria in Microsoft Excel 2010

In Excel, with a specific criteria we can add the sum of multiple columns. We can create a formula using the Sum, If and Sumproduct functions.

**Let’s take an example:**

- Column A has Pen, Pencil, etc.
- Regions in columns B:E i.e. North, East, West, South as shown in the below picture.
- Here, we want to find the total value for
**Pen**in all the regions

- In cell E13 we want to show the total value of Pen in the range B2:E10
- To get the result in cell E13,the formula would be =
**SUMPRODUCT((A2:A10=”Pen”)*(B2:E10))** - This is what our result looks like -

If you manually add up the values for columns B:E where column A contains “Pen”, you will see that it adds up to 99, which we have also derived from the formula.

Now lets understand the Sumproduct function.

**SUMPRODUCT:** SUMPRODUCT function multiplies the corresponding items in the arrays and returns the sum of the results.

We can follow another method to do the same calculation. We can use Sum & IF together to get the desired result as follows:

Formula in cell E14 would be =**SUM(IF(A2:A10=”Pen”,B2:E10))**along with **CTRL + SHIFT + ENTER**

This is an array formula & has to be enclosed with **CSE**(Ctrl + Shift + Enter keys on the keyboard).

**IF:** Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

Logical test is the condition or a value that you want to test.

**value_if_true** is optional. It is the value that is returned if the condition is TRUE.

**value_if_false** is optional. It is the value that is return if the condition is FALSE.

**Sum:** Adds all the numbers in a range of cells

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

SumIf ?

Hi Linn

Check this link:- http://www.exceltip.com/tips/how-to-use-sumproduct-with-multiple-criteria.html

Wonderfull!

Thank you very much for this tip