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.
Note If you open the referenced workbook, the formula works correctly.
ExamplesNote You must enter each formula as an array formula. To enter an array formula in Microsoft Excel for Windows, press CTRL+SHIFT+ENTER.
SUMIFInstead of using a formula that is similar to the following:
COUNTIFInstead of using a formula that is similar to the following:
COUNTBLANKInstead of using a formula that is similar to the following:
For more information, click the following article number to view the article in the Microsoft Knowledge Base:
275165 When to use a SUM(IF()) array formula
Use a logical AND or OR to replace the SUMIFS or COUNTIFS function
267982 How to use a logical AND or OR in a SUM+IF statement in Excel
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.
Article ID: 260415 - Last Review: Oct 26, 2017 - Revision: 1