HOW TO SUMIFS EXACT MATCH IN EXCEL

By default, the SUMIFS function is not case-sensitive. To make it case-sensitive and do an exact match, follow the below steps:

SUMIFS COMPLETE EXACT MATCH

Alternative 1: Using a combination of SUM, IF & EXACT

We shall start by doing a complete exact match then a partial exact match

How do you sum up all sales for FURNITURE ignoring the furniture products?

How it Works

Since the SUMIFS function cannot handle an array calculation, we have to use an alternative combination of SUM, IF & EXACT functions

EXACT(F2,Table10[Product Category])→ Returns an array of TRUE if it finds and exact match else FALSE

IF function returns the sales value if the returned array is TRUE ► {FALSE;143;FALSE;FALSE;FALSE;55;FALSE;FALSE;FALSE;FALSE;FALSE;244}

SUM function ignores the FALSE values and sums the returned sales

=SUM( {FALSE;143;FALSE;FALSE;FALSE;55;FALSE;FALSE;FALSE;FALSE;FALSE;244} )

Alternative 2: Using SUMPRODUCT Function

This is a much smaller function and easy to understand

EXACT(F2, Table10[Product Category])*Table10[Sales]► When you multiply the TRUE/FALSE array that is returned by the EXACT function with Sales values, it returns {0;143;0;0;0;55;0;0;0;0;0;244}…zero where data is FALSE

SUMPRODUCT function just sums the returned array of numbers

SUMIFS PARTIAL EXACT MATCH

Alternative 1: Using a combination of SUM, IF, ISNUMBER & FIND

For the partial match, the EXACT function will not work, therefore we have to use a combination of ISNUMBER & FIND functions.

How It Works

Unlike the SEARCH function, the FIND function returns the position of a specific text within a text string and it is case sensitive.

If it does not find the substring, it returns a #Value Error.

FIND(F2, Table10[Cost Code])► Returns an array of Numbers and Errors {#VALUE!;#VALUE!;#VALUE!;5;#VALUE!;5;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;5}

Convert above array into TRUE/FALSE by nesting it inside the ISNUMBER Function ISNUMBER({#VALUE!;#VALUE!;#VALUE!;5;#VALUE!;5;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;5}) is converted into {FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE}

Using IF function return sales amount where the logical test is TRUE. Finally, SUM adds up these sales amounts.

Alternative 2: Using a combination of SUMPRODUCT, ISNUMBER & FIND

When you multiply the {FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE} with the sales amount, you get {0;0;0;146;0;55;0;0;0;0;0;244}

DOWNLOAD WORKSHEET