SUMPRODUCT and IF function

Author: Oscar Cronquist Article last updated on December 03, 2021

You don't need to use the IF function in a SUMPRODUCT function, it is enough to use a logical expression. For example, the array formula above in cell B12 counts all cells in C3:C9 that are above 5 using an IF function.

How to simplify IF functions in the SUMPRODUCT function
SUMPRODUCT if greater than 0 (zero)
SUMPRODUCT if between two dates
SUMPRODUCT if cell contains text
SUMPRODUCT if or
Get Excel file

SUMPRODUCT if cell equals text (Link)
SUMPRODUCT if cell equals text from list (Link)

1. How to simplify IF functions in the SUMPRODUCT function

=SUMPRODUCT(IF(C3:C9>5,1,0))

The first argument in the IF function is a logical expression, use that in your SUMPRODUCT formula. The formula in B13 does the same thing as in B12.

=SUMPRODUCT((C3:C9>5)*1)

You need to tell Excel the order of calculation, in other words, make sure you calculate C3:C9>5 before you multiply by 1. To do that use parentheses.

(C3:C9>5)*1

The larger than comparison operator compares each value in C3:C9 if larger than 5. It returns an array the same size as C3:C9 containing boolean values, TRUE or FALSE.

{FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE}*1

The picture above shows the array in column D, value 7 and 8 are larger than 5.

The SUMPRODUCT function can't sum boolean values so multiply (using the asterisk *) the logical expression by 1 to convert the boolean values (TRUE and FALSE) to numbers (1 or 0).

{0;0;1;0;1;0;0}

The asterisk also has another advantage, you can enter the SUMPRODUCT formula as a regular formula.

1.1 Combine SUMPRODUCT and IF

The picture above demonstrates an IF function that checks if a condition is met and if TRUE returns a value in a corresponding cell.

There is no need for the IF function in the above example, simply use the logical expression and multiply with the values you want to use, demonstrated in cell B13.

B3:B9="A" returns {TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE} displayed in column A in image above.

(B3:B9="A")*C3:C9 returns this array: {2;0;7;0;8;0;0} shown in column A.

Lastly, the SUMPRODUCT function sums all the numbers in the array returning 17 in cell B12.

2 + 7 + 8 = 17.

2. SUMPRODUCT if greater than 0 (zero)

SUMPRODUCT greater than 0

The formula in cell D3 adds numbers from B3:B9 if they are larger than 0 (zero) and returns a total.

Formula in cell D3:

=SUMPRODUCT((B3:B9>0)*B3:B9)

2.1 Explaining formula

Step 1 - Check if values are larger than 0 (zero)

The larger than sign is a boolean operator that compares values to a condition, it returns TRUE or FALSE based on the outcome.

B3:B9>0

becomes

{-8; -1; 4; 5; -6; 7; 4}>0

and returns

{FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE}.

Step 2 - Multiply boolean values with numbers

The parentheses let you control the order of calculation, we must do the comparisons before we multiply.

(B3:B9>0)*B3:B9

The asterisk character lets you multiply a value with another value, in this case, with a boolean value.

(B3:B9>0)*B3:B9

becomes

{FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE}*B3:B9

TRUE is equal to 1 and FALSE is equal to 0 (zero), this will create the following outcomes:

TRUE * number = number
FALSE * number = 0 (zero)

{FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE}*B3:B9

becomes

{FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE}*{-8; -1; 4; 5; -6; 7; 4}

and returns {0; 0; 4; 5; 0; 7; 4}.

Notice how all the negative numbers are converted to 0's (zeros).

Step 3 - Add numbers and return total

The SUMPRODUCT function calculates the product of corresponding values and then returns the sum of each multiplication.

SUMPRODUCT(array1, [array2], ...)

SUMPRODUCT((B3:B9>0)*B3:B9)

becomes

SUMPRODUCT({0; 0; 4; 5; 0; 7; 4})

and returns 20 in cell D3.

SUMPRODUCT greater than 0

3. SUMPRODUCT if between two dates

SUMPRODUCT between two dates

The SUMPRODUCT function shown in cell F5 calculates a total based on two dates. The example above demonstrates the start date in F2 and end date in F3, cells B5, B6, and B7 have dates that match the date range.

The corresponding numbers are in cells C5, C6, and C7. The total is calculated like this 4 + 5 - 6 equals 3.

Formula in cell f5:

=SUMPRODUCT((B3:B9>=F2)*(B3:B9<=F3)*C3:C9)

3.1 Explaining formula

Step 1 - Check which dates are equal to or later than the start date

The larger than and the equal signs are boolean operators that compare values to a condition, it returns TRUE or FALSE based on the outcome.

B3:B9>=F2

becomes

{44197; 44198; 44199; 44200; 44201; 44202; 44203}>=44199

and returns

{FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE}.

Step 2 - Check which dates are equal to or earlier than the end date

The less than and the equal signs are boolean operators that compare values to a condition, it returns TRUE or FALSE based on the outcome.

B3:B9<=F3

becomes

{44197; 44198; 44199; 44200; 44201; 44202; 44203}<=44201

and returns

{TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE}.

Step 3 - Multiply arrays

The asterisk character lets you multiply values, this works fine with boolean values as well. This means that you can apply AND logic between the arrays.

TRUE*TRUE = 1
TRUE*FALSE = 0 (zero)
FALSE*TRUE= 0 (zero)
FALSE*FALSE= 0 (zero)

(B3:B9>=F2)*(B3:B9<=F3)

becomes

{FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE}*{TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE}

and returns {0; 0; 1; 1; 1; 0; 0}.

Step 4 - Multiply array with numbers

(B3:B9>=F2)*(B3:B9<=F3)*C3:C9

becomes

{0; 0; 1; 1; 1; 0; 0}*C3:C9

becomes

{0; 0; 1; 1; 1; 0; 0}*{-8; -1; 4; 5; -6; 7; 4}

and returns {0; 0; 4; 5; -6; 0; 0}.

Step 5 - Add numbers and return a total

The SUMPRODUCT function calculates the product of corresponding values and then returns the sum of each multiplication.

SUMPRODUCT(array1, [array2], ...)

SUMPRODUCT((B3:B9>=F2)*(B3:B9<=F3)*C3:C9)

becomes

SUMPRODUCT({0; 0; 4; 5; -6; 0; 0})

and returns 3 in cell F5. 4 + 5 - 6 = 3

4. SUMPRODUCT if cell contains text

SUMPRODUCT contains string
The SUMPRODUCT function displayed in cell F4 calculates a total based on a string specified in cell F2. If a cell in B3:B9 contains the string (partial match) the corresponding number in cell C3:C9 is added to a total.

The image above demonstrates that the string is found in cells B4, B6, and B9. The corresponding cells in column C are C4, C6, and C9. The total is calculated like this: -1 + 5 + 4 equals 8.

Formula in cell F4:

=SUMPRODUCT(ISNUMBER(SEARCH(F2, B3:B9))*C3:C9)

4.1 Explaining formula

Step 1 - Find cells containing the string

The SEARCH function returns a number representing the position of character at which a specific text string is found reading left to right. It is not a case-sensitive search.

SEARCH(find_text,within_text, [start_num])

SEARCH(F2, B3:B9)

becomes

SEARCH("ea",{"apple";"pear";"banana";"peach";"orange";"lime";"pineapple"})

and returns {#VALUE!; 2; #VALUE!; 2; #VALUE!; #VALUE!; 4}.

The SEARCH function returns an error value if the string is not found.

Step 2 - Convert result to boolean values

The error values break the formula, we need to take care of possible error values.

The ISNUMBER function checks if a value is a number, returns TRUE or FALSE. This works fine with error values as well.

ISNUMBER(value)

ISNUMBER(SEARCH(F2, B3:B9))

becomes

ISNUMBER({#VALUE!; 2; #VALUE!; 2; #VALUE!; #VALUE!; 4})

and returns {FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; TRUE}.

Notice that the error values are now boolean value FALSE.

Step 3 - Multiply with numbers

Boolean values can be used in multiplication, TRUE has the numerical equivalent of 1 and FALSE is 0 (zero).

TRUE * number = number
FALSE * number = 0 (zero)

ISNUMBER(SEARCH(F2, B3:B9))*C3:C9

becomes

{FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; TRUE}* {-8; -1; 4; 5; -6; 7; 4}

and returns {0; -1; 0; 5; 0; 0; 4}.

Step 4 - Add values and return total

SUMPRODUCT(ISNUMBER(SEARCH(F2, B3:B9))*C3:C9)

5. SUMPRODUCT if or

SUMPRODUCT if or1

The SUMPRODUCT function in cell H3 adds numbers from D3:D9 if the corresponding cell in B3:B9 equals the value in cell F3 or C3:C9 is above the value specified in cell F6.

Formula in cell H3:

=SUMPRODUCT((((B3:B9=F3)+(C3:C9>F6))>0)*D3:D9)

5.1 Explaining formula

Step 1 - Evaluate condition

The equal sign compares value to value, however, not considering upper and lower cases. You need the EXACT function to do that.

B3:B9=F3

becomes

{"A"; "B"; "A"; "B"; "A"; "B"; "A"}="A"

and returns

{TRUE; FALSE; TRUE; FALSE; TRUE; FALSE; TRUE}.

Step 2 - Check if the number is larger than the condition

The larger than sign compares C3:C9 to number in F6, the result is a boolean value TRUE or FALSE.

C3:C9>F6

becomes

{6; 10; 5; 7; 2; 5; 9}>5

and returns

{TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; TRUE}.

Step 3 - Perform OR logic between arrays

The plus sign adds value to value, this applies OR logic between the arrays.

TRUE + TRUE = 1
TRUE + FALSE = 1
FALSE + TRUE = 1
FALSE + FALSE = 0 (zero)

This means that at least one condition must be met.

(B3:B9=F3) + (C3:C9>F6)

becomes

{TRUE; FALSE; TRUE; FALSE; TRUE; FALSE; TRUE} + {TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; TRUE}

equals {2; 1; 1; 1; 1; 0; 2}.

In Excel, any other number than 0 (zero) is considered to be TRUE even negative numbers. FALSE is 0 (zero).

Step 4 - Check if values in array are larger than 0 (zero)

((B3:B9=F3)+(C3:C9>F6))>0

becomes

{2; 1; 1; 1; 1; 0; 2}>0

and returns {TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE}.

Step 5 - Multiply with numbers

(((B3:B9=F3)+(C3:C9>F6))>0)*D3:D9

becomes

{TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE}*D3:D9

and returns

{30; 70; 40; 30; 50; 0; 60}.

Step 6 - Sum numbers in array

SUMPRODUCT((((B3:B9=F3)+(C3:C9>F6))>0)*D3:D9)

becomes

SUMPRODUCT({30; 70; 40; 30; 50; 0; 60})

and returns 280 in cell H3.

Get Excel *.xlsx file

Get the Excel file

SUMPRODUCT-IF-functionv2.xlsx

Functions in this article

SUMPRODUCT IF FALSE TRUE

More than 1300 Excel formulas

Excel categories

Home page

Latest updated articles.

Excel Functions

More than 300 Excel functions with detailed information including syntax, arguments, return values, and examples for most of the functions used in Excel formulas.

Excel Formulas

More than 1300 formulas organized in subcategories.

Excel Tables

Excel Tables simplifies your work with data, adding or removing data, filtering, totals, sorting, enhance readability using cell formatting, cell references, formulas, and more.

Advanced Filter

Allows you to filter data based on selected value , a given text, or other criteria. It also lets you filter existing data or move filtered values to a new location.

Data Validation

Lets you control what a user can type into a cell. It allows you to specifiy conditions and show a custom message if entered data is not valid.

Drop Down List

Lets the user work more efficiently by showing a list that the user can select a value from. This lets you control what is shown in the list and is faster than typing into a cell.

Named Ranges

Lets you name one or more cells, this makes it easier to find cells using the Name box, read and understand formulas containing names instead of cell references.

Excel Solver

The Excel Solver is a free add-in that uses objective cells, constraints based on formulas on a worksheet to perform what-if analysis and other decision problems like permutations and combinations.

Charts

An Excel feature that lets you visualize data in a graph.

Conditonal Formatting

Format cells or cell values based a condition or criteria, there a multiple built-in Conditional Formatting tools you can use or use a custom-made conditional formatting formula.

Pivot Tables

Lets you quickly summarize vast amounts of data in a very user-friendly way. This powerful Excel feature lets you then analyze, organize and categorize important data efficiently.

VBA

VBA stands for Visual Basic for Applications and is a computer programming language developed by Microsoft, it allows you to automate time-consuming tasks and create custom functions.

Macros

A program or subroutine built in VBA that anyone can create. Use the macro-recorder to quickly create your own VBA macros.

UDFs

UDF stands for User Defined Functions and is custom built functions anyone can create.

One Response to “SUMPRODUCT and IF function”

Rauf says:

August 26, 2022 at 12:23 am

Names Grades
Abid 2
Abid 2
2
Abid 2
#DIV/0! 2
2 1
2 1
2 1
#NAME? 9
Basit 9
Basit 9
=SUMPRODUCT(NOT(ISERROR(A2:A12""))*1/COUNTIF(A2:A12,A2:A12&""))
Here it shows result 4 in stead of 3
This formula is correctly working with Errors but not agnoring the Blanks
Kindly help me

Reply

How to comment

How to add a formula to your comment
<code>Insert your formula here.</code>

Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >

How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.

Contact Oscar

You can contact me through this contact form

SUMPRODUCT and IF function

Table of contents

1. How to simplify IF functions in the SUMPRODUCT function

1.1 Combine SUMPRODUCT and IF

2. SUMPRODUCT if greater than 0 (zero)

2.1 Explaining formula

Step 1 - Check if values are larger than 0 (zero)

Step 2 - Multiply boolean values with numbers

Step 3 - Add numbers and return total

3. SUMPRODUCT if between two dates

3.1 Explaining formula

Step 1 - Check which dates are equal to or later than the start date

Step 2 - Check which dates are equal to or earlier than the end date

Step 3 - Multiply arrays

Step 4 - Multiply array with numbers

Step 5 - Add numbers and return a total

4. SUMPRODUCT if cell contains text

4.1 Explaining formula

Step 1 - Find cells containing the string

Step 2 - Convert result to boolean values

Step 3 - Multiply with numbers

Step 4 - Add values and return total

5. SUMPRODUCT if or

5.1 Explaining formula

Step 1 - Evaluate condition

Step 2 - Check if the number is larger than the condition

Step 3 - Perform OR logic between arrays

Step 4 - Check if values in array are larger than 0 (zero)

Step 5 - Multiply with numbers

Step 6 - Sum numbers in array

Get Excel *.xlsx file

Get the Excel file

Functions in this article

Excel categories

One Response to “SUMPRODUCT and IF function”

Leave a Reply

How to comment