Microsoft KB Archive/79138

{|
 * width="100%"|

Excel: Calculating a SUM+IF on a Discontinuous Selection

 * }

-

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)