Leading the way in Microsoft Office Development

Summing Values based on Conditions

It is also possible to use the SUMPRODUCT() function to perform conditional summing and counting.

There is a built-in function SUMIF() specifically for this task but it only allows you to define a single condition.

When you want to perform summing based on multiple conditions you have to use Array Formulas with the SUM() and IF() functions.

The Conditional Sum Wizard add-in will help you to create these formulas.

Alternatively you can also use a Pivot Tables to filter out your data based on conditions.

Working with array formulas though is not particularly easy and the SUMPRODUCT() function provides you with an alternative.

Summing - One Condition

If you only have one condition it is much more efficient to use the SUMIF function rather than the SUMPRODUCT.

Summing - Two Conditions

It is also possible to use this function to conditionally sum multiple columns.

Lets consider you had a table of data with values in column "E" and column "F".

You could use the following formula to sum all the values in both columns.

=SUMPRODUCT( (B3:B12="London")*(C3:C12="May")*( (E3:E12)+(F3:F12) ) )

Summing with Conditions - THESE ARE REFERRING TO DIFFERENT SCREEN SHOTS

It is also possible to include conditions with your cell ranges.

The following example returns the total number of hours worked on a Monday.

These conditions will be automatically replaced with their corresponding arrays.

This formula automatically replaces the actual condition with an array of corresponding 1's and 0's.

The 1's are used to represent when the condition is True and the 0's are used to represent when the condition is False.

This function is actually using the following arguments:

It is also possible to include more than one condition and return the sum of the corresponding cells.

The following example returns the total number of hours worked on Monday OR Wednesday.

The plus operator can be used for OR conditions.

Remember to include the two boolean conditions in an extra set of parentheses.

Counting Values based with Conditions

There is also an COUNTIF() function

For example if you want the total number of cells that match multiple conditions you can use a plus sign.

Counting - One Condition

If you only have one condition it is much more efficient to use the COUNTIF function rather than the SUMPRODUCT.

Counting - Two Conditions

The following formula will return the number of cells in the range that either contain the text "Wed" or the text "Thu".!!!!

The following formula will return the number of cells in the range that have a "Jan" in column "B" and a "Wed" in column "C"

It is important to remember that you always need to multiple a condition with another cell range (or the number 1).

It is also possible to include more than one condition and return the total number of cells that match a criteria.

The following example returns the number of cells in the range that either match both January AND Thursday.

The asterisk operator can be used for AND conditions.

More Advantages

Being able to easily define multiple conditions is not the only advantage the SUMPRODUCT() function has over the SUM(IF( functions.

Here are two more advantages that are quite significant.

SUMPRODUCT can reference and update from closed workbooks.

Things to Remember

In the majority of cases you do not have to enter this function using (Ctrl + Shift + Enter).

The only exception to this is if you embed a function that requires you to use (Ctrl + Shift + Enter) but in this case you have to enter the function as an array formula so you might as well just use the SUM() function.

Instead of multiplying your cell ranges by 1 ( *1 ) you can alternatively add 0 ( + 0) or use two minus signs ( -- ).

The plus sign ( + ) can be used to indicate an OR condition when the cell ranges are the same.

The plus sign ( + ) can be used to indicate an AND condition when the cell ranges are different.