Download Excel Start File: [ Ссылка ]

Download File: [ Ссылка ]
Learn how to create EXACT & SUMPRODUCT functions array formula to count with Case Sensitive Criteria in Excel:
1. (00:09) Intro to topic and look back to #181 Excel Magic Trick made 7 years ago!
2. (00:41) Look at problems with trying to create a formula that can count with Case Sensitive Criteria, including COUNTIF and FIND functions.
3. (01:31) EXACT function to check whether two text strings are the same including Case.
4. (01:47) COUNTIF and similar functions cannot handle array operations.
5. (01:59) Start formula with a function argument array operation in the EXACT function.
6. (02:54) Wrap COUNTIF around EXACT function and array operation and test to see if this is allowed. It is NOT allowed. The range argument in the COUNTIF function cannot handle array operations. See the useless Error Dialog box that COUNTIF delivers…
7. (03:34) Wrap SUMPRODUCT around EXACT function and array operation and see that SUMPRODUCT can handle the array operation, but that it cannot “see” the TRUE FALSE values.
8. (04:09) Use Double Negative to convert the TRUE and FALSE values to ones (1) and zeroes (0) so that SUMPRODUCT can understand and count the values that match the Case Sensitive Criteria. Use Double Negative because, of all the math operations that can convert TRUE and FALSE values to ones (1) and zeroes (0), it is the most efficient.
9. (05:10) Prove that Double Negative converts the TRUE and FALSE values to ones (1) and zeroes (0) by using the F9 key and then the Esc key (because we want to revert back to the cell contents before we used F2 key to put formula in edit mode.


Also see:
Trick 1246: Counting with Case Sensitive Criteria: EXACT & SUMPRODUCT
Excel Magic Trick 1247: Adding with Case Sensitive Criteria: EXACT & SUMPRODUCT
Excel Magic Trick 1248: Add or Count Case Sensitive Contains Criteria: ISNUMBER, FIND, SUMPRODUCT

свернуть