TheSUMIF functionand theSUMIFS functionare both good options for counting text values, and both functions supportwildcards.
However, neither function is case-sensitive, so they can’t be used to solve this problem.
A good solution is to use theEXACT functionwith theSUMPRODUCT function, as explained below.
EXACT function
The EXACT function has just one purpose: to compare text in a case-sensitive manner.
EXACT takes twoarguments:text1andtext2.Iftext1andtext2match exactly (considering upper and lower case), EXACT returns TRUE.
For convenience,code(B5:B15) andqty(C5:C15) arenamed ranges.
Each FALSE represents a non-match.
In this case, the array operation is performed by the EXACT function in the previous step.
InExcel 365, you’ve got the option to replace SUMPRODUCT with theSUM function.
To read more about this, seeWhy SUMPRODUCT?
Related videos
Boolean operations in array formulas
