Excel: Calculating a SUM+IF on a Discontinuous Selection

Last reviewed: November 3, 1994
Article ID: Q79138

The information in this article applies to:

Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 5.0
Microsoft Excel for OS/2, versions 2.2, 3.0

SUMMARY

To calculate a SUM(IF()) on a discontiguous range, separate IF functions must be placed in the SUM function as arguments.

MORE INFORMATION

For example, assume that we want to return the sum of all values that equal 10 in the range A1:A12 and in G16:G57. The following formula, entered as an array, will work correctly on up to 14 noncontiguous areas:

   {=SUM(IF(A1:A12=10,1,0),IF(G16:G57=10,1,0))}

However, there are variations of the SUM(IF()) that will either result in the #VALUE! or #N/A error. Here are two variations that will not work:

   {=SUM(IF((A1:A12,G16:G57)=10,1,0))} returns a #VALUE!, and

   {=SUM(IF(A1:A12,G16:G57=10,1))} returns a #N/A.

Note: for Microsoft Excel version 5.0, an easier method is available that does not involve an array formula. Use the following formula:

   =COUNTIF(A1:A12,10)+COUNTIF(G16:G57,10)

REFERENCES

"Microsoft Excel Function Reference," version 3.0, pages 231-232

KBCategory: kbusage
KBSubcategory:
Additional reference words: 2.10 2.1 2.10c 2.1c 2.10d 2.1d 2.20 2.21
3.00 4.0 4.00 5.0 5.00

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 3, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.