Excel: Calculating a SUM+IF on a Discontinuous Selection

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

Additional query words: 2.10 2.1 2.10c 2.1c 2.10d 2.1d 2.20 2.21 4.0 5.0


Keywords          : 
Version           : 
Platform          : 
Issue type        :

Last Reviewed: March 23, 1999