Microsoft KB Archive/98883

= Microsoft Knowledge Base =

Excel: Making SUM+IF Statement Case-Sensitive Using EXACT
Last reviewed: September 12, 1996

Article ID: Q98883

The information in this article applies to:


 * Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 4.0a, 5.0
 * Microsoft Excel for OS/2, versions 2.2, 3.0
 * Microsoft Excel for the Macintosh, versions 2.2, 3.0 ,4.0

SUMMARY
In Microsoft Excel, the EXACT function compares two text strings and returns TRUE if the text strings are exactly the same or FALSE if they are not the same. The EXACT function is case-sensitive.

If you are using a SUM(IF) array formula, to count the number of occurrences of a certain text string in a range of cells, by default this formula does not distinguish between case. For example, if you search for the word &quot;test,&quot; the array formula will count both &quot;Test&quot; and &quot;test&quot; as valid entries. To make the SUM(IF) formula case-sensitive, use the EXACT function, as in the following example.

To demonstrate how to make a SUM(IF) formula, case-sensitive:

  Type the following in the indicated cells: A1:  test A2:  Microsoft A3:  Excel A4:  Test   In cell A5 type the following formula: =SUM(IF(EXACT(&quot;Test&quot;,A1:A4),1,0)) Note: You must enter this formula as an array (press     CTRL+SHIFT+ENTER). This formula will count the number of times that the text string &quot;Test&quot; appears in the range from A1 to A4. In this example, the formula above will return a 1. This formula will not count the string &quot;test&quot; since it is looking for an exact match (including case). 

If you do not want the SUM(IF) array formula to distinguish between case, use the following formula:

=SUM(IF(A1:A4=&quot;test&quot;,1,0)) You must enter this formula as an array (press the CTRL+SHIFT+ENTER).