This problem can be solved with the COUNTIF function or the MATCH function, as explained below.
Both approaches work well.
The advantage of the MATCH approach is that it will work witharraysorranges.
The COUNTIF function is limited to ranges only, likeother functions in this group.
The 0s indicate names in B5:B16 that don’t appear in D5:D12.
In other words, we need to convert the 1s to 0s and the 0s to 1s.
Before we sum, we need to convert the TRUE and FALSE values to 1s and 0s again.
InLegacy Excel, this is anarray formulaand must be entered with control + shift + enter.
TheMATCH functionreturns the position of a value in a range.
These values can be numbers, cell references, ranges, arrays, and constants, in any combination.
SUM can handle up to 255 individual arguments.
SUMPRODUCT Function
The Excel SUMPRODUCT function multipliesrangesorarraystogether and returns the sum of products.
MATCH supports approximate and exact matching, andwildcards(* ?)
