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

Table of Contents

## 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}

## Recent Comments