Microsoft KB Archive/66759

{| = Comparing Two Ranges of Data and Returning a Single Value =
 * width="100%"|

Last reviewed: November 2, 1994

Article ID: Q66759

SUMMARY
You may want to compare two ranges of data to see whether or not the ranges are equal, and then return a particular value if they are equal. (That is, if every cell of one array equals every adjacent cell of another array, then you should return a particular value.)

For example:

A1: 2  B1:  2  C1:  1 A2: 3  B2:  3  C2:  2 A3: 4  B3:  4  C3:  3 A4: 1  B4:  1  C4:  4 You may want to compare the values in columns A and B. If all corresponding values are equal, you should return the sum of the values in column C. Typing {=IF(A1:A4=B1:B4,SUM(C1:C4))} does not work. This tests only cells A1 and B1 for equality and returns the SUM, even if all other values in columns A and B are inequal. To obtain the proper results, type the following: =IF(AND(A1:A4=B1:B4),SUM(C1:C4),&quot;&quot;) This is typical of any comparison where you want to apply a test to two ranges of cells and return a value in a single cell. Thus, most functions could be used in place of the SUM function above.
 * }