Documentation
-
Use a logical AND or OR in a SUM+IF statement - Microsoft 365 Apps
Describes how to use a logical AND or OR in a SUM+IF statement in Excel.
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
A formula that contains the SUMIF, SUMIFS, COUNTIF, COUNTIFS, or COUNTBLANK functions may return the "#VALUE!" error in Microsoft Excel.
Note
This behavior also applies to the Dfunctions, such as DAVERAGE, DCOUNT, DCOUNTA, DGET, DMAX, DMIN, DPRODUCT, DSTDEV, DSTDEVP, DSUM, DVAR, and DVARP. OFFSET and INDIRECT functions also have this behavior.
This behavior occurs when the formula that contains the function refers to cells in a closed workbook and the cells are calculated.
Note
If you open the referenced workbook, the formula works correctly.
To work around this behavior, use a combination of the SUM and IF functions together in an array formula.
Note
You must enter each formula as an array formula. To enter an array formula in Microsoft Excel for Windows, press CTRL+SHIFT+ENTER.
Instead of using a formula that is similar to the following:
=SUMIF([Source]Sheet1!$A$1:$A$8,"a",[Source]Sheet1!$B$1:$B$8)
Use the following formula:
=SUM(IF([Source]Sheet1!$A$1:$A$8="a",[Source]Sheet1!$B$1:$B$8,0))
Instead of using a formula that is similar to the following:
=COUNTIF([Source]Sheet1!$A$1:$A$8,"a")
use the following formula:
=SUM(IF([Source]Sheet1!$A$1:$A$8="a",1,0))
Instead of using a formula that is similar to the following:
=COUNTBLANK([Source]Sheet1!$A$1:$A$8)
use the following formula:
=SUM(IF([Source]Sheet1!$A$1:$A$8="",1,0))
When to use a SUM(IF()) array formula, use a logical AND or OR to replace the SUMIFS or COUNTIFS function.
This behavior is by design.
The SUMIF function uses the following syntax:
=SUMIF(range, criteria, sum_range).
See How to correct a #VALUE! error for more information.
For more information about a wizard that can help you create these functions, click Microsoft Excel Help on the Help menu, type summarize values that meet conditions by using the conditional sum wizard in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
For more information about array formulas, click Microsoft Excel Help on the Help menu, type about using formulas to calculate values on other worksheets and workbooks in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Documentation
Use a logical AND or OR in a SUM+IF statement - Microsoft 365 Apps
Describes how to use a logical AND or OR in a SUM+IF statement in Excel.
Training
Module
Create formulas that use tables, records, and collections in a canvas app in Power Apps - Training
Do you have need for complex formulas in your app? This module can help you write those formulas.