Sumproduct And CountIf To Find Missing Value
DEFINITION: The Sumproduct Function Multiplies Ranges Or Arrays Together And Returns The Sum Of Products. The Countif function count the number of values occurs in a range or array. Trim function removes the un necessary space from a cell.
IN THIS EXAMPLE :- In this example we will use 3 function Sumproduct - Countif - Trim to compare the two database in a range and will find the missing value if they are not available. To refer individually please check this post SUMPRODUCT In EXCEL. / COUNTIF IN EXCEL / TRIM FUNCTION.
To do this we will use help of hyphens or you can say a 2 minus sign (--) before giving the range reference. It actually convert and check the condition with true and false and return 0 for false and 1 for true. For more detail please check below step with image a...