How to Sum if Cell Contains Text in Another Column

We often sum data based on criteria. To sum values based on a cell, in fact, we sum values based on the text in this cell. If the text is included in another table another column, we need to find out all locations of this text in this column, and then sum up all matched values to the column.

In this article, we will show you the formula which can ‘sum if cell contains text in another column’ with the application of SUMIFS function. Through a simple instance, we will introduce you the syntax, arguments of SUMIFS function, and let you know how each part within the formula works for getting the correct result we expect. After reading the article, you can have a simple understanding of SUMIFS function, and you may use it properly in more cases.

EXAMPLE

Sum if Cell Contains Text in Another Column 1

In the left table, we list different kinds of fruits in column A, some are duplicate. We list amounts for these kinds of fruits in column B.

In the right table, we enter one kind of fruit in cell D2, it is included in column A fruit list. It is a dynamic value, we can enter any kind of fruit into this field, just make sure that the entered fruit always exists in column A. Then, though entering a formula in ‘Total’ E2, we can get proper total amount dynamically based on the entered fruit in D2.

Above all, to edit this formula, we will apply Excel SUMIFS function.

FORMULA – SUMIFS FUNCTION

Step 1: In E2, enter the formula =SUMIFS(B2:B9,A2:A9,D2).

Sum if Cell Contains Text in Another Column 2

Step 2: Press Enter after typing the formula.

Sum if Cell Contains Text in Another Column 3

We can see in column A there are two cells contain text ‘Orange’, the corresponding amounts are 200 and 130, so the total is 200+130=330. It can be seen the formula works correctly.

SUMIFS FUNCTION INTRODUCTION

SUMIFS function can be seen as SUM+IFS, it supports multiple ‘criteria range’ and ‘criteria’ combinations.

For SUMIFS function, the syntax is:

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …).

Criteria range N and criteria N are optional, so it still works if only one criteria range and one criterion exist.

To let you know the application of SUMIFS function in our formula in this instance, we will explain each argument in SUMIFS function, and split the formula to several parts, let you know how it works from inside to outside.

ALL ARGUMENTS

SUMIFS – SUM RANGE

In our instance, B2:B9 is the sum range, it provides values to sum up. If we want to set it as an absolute range, we can add $ before column and row to lock the range, like this $B$2:$B$9. Then when copying formula to other cells, this range is fixed.

Actually, in range B2:B9, each cell saves a value, if we save these values in an array, it is:

{100;120;200;150;130;230;150;250}

In formula bar, select B2:B9, press F9, values in this range are listed in an array.

SUMIFS – CRITERIA RANGE

A2:A9 is the criteria range, in this case we have only one criteria range. Similar with B2:B9, it records all fruits, some of them are duplicate, if we list them in an array, it looks like:

{“Apple”;”Banana”;”Orange”;”Peach”;”Orange”;”Lychee”;”Apple”;”Watermelon”}

In the formula bar, it displays:

Sum if Cell Contains Text in Another Column 5

SUMIFS – CRITERIA

In this formula, D2 is the criteria, it contains text ‘Orange’.

Sum if Cell Contains Text in Another Column 6

HOW FORMULA WORKS

After introducing each argument, we get two arrays and a text, see the formula below:

=SUMIFS({100;120;200;150;130;230;150;250},{“Apple”;”Banana”;”Orange”;”Peach”;”Orange”;”Lychee”;”Apple”;

“Watermelon”},”Orange”)

Sum if Cell Contains Text in Another Column 7

In the formula, the criteria range and criteria are:

Criteria range: {“Apple”;”Banana”;”Orange”;”Peach”;”Orange”;”Lychee”;”Apple”;”Watermelon”}

Criteria: “Orange”

In the range, “Orange” occurs twice; compare each value in array with text “Orange”, only the two fields are True, others are False.

So, after comparing with criteria range with criteria, we get below array:

{False;False;True;False;True;Flase;False;False}

Convert ‘True’ to ‘1’ and ‘False’ to ‘0’:

{0;0;1;0;1;0;0;0}

Now, we have below two arrays:

Array 1: {100;120;200;150;130;230;150;250} – sum range

Array 2: {0;0;1;0;1;0;0;0} – criteria

The two arrays are matched. So, in array1, value will be saved only if its corresponding value in array2 is “1”, otherwise, “0” will be recorded in current array instead of the original value.

Refer to above rule, we get an updated array1:

Array 1: {0;0;200;0;130;0;0;0} – updated sum range

Now, sum up existing values in array1.

0+0+200+0+130+0+0+0=330

After all, we get the result 330.

COMMENTS

1. We can enter another fruit into D2, the total value is updated accordingly.

Sum if Cell Contains Text in Another Column 8

2. In this formula, instead of enter cell reference ‘D2’, we can enter ‘Orange’ into formula directly.

Sum if Cell Contains Text in Another Column 9

Be aware that you have to add double quotes “” and make sure the text is within “”.

If you forgot “”, then “0” is returned.

Sum if Cell Contains Text in Another Column 10

3. If in column A, cell contains text “Orange”, but not equals to “Orange”, the formula cannot work properly. See example below:

Sum if Cell Contains Text in Another Column 11

In this case, we need to add “*” before and after “Orange”.

Sum if Cell Contains Text in Another Column 12

Or add “*” before and after D2 (without double quotes).

Sum if Cell Contains Text in Another Column 13

Actually, SUMIFS function can support wildcards in its formula, and it requires to add double quotes (““) to enclose wildcards or texts.

  1. An asterisk (*) means one or more characters.
  2. A question mark (?) means one character.
  3. The position of asterisk or question mark means the character(s) position relative to the entered part.

4. You can define sum range and criteria range with proper names, then enter defined names into formula respectively.

For example, select range A2:A9, in ‘Name Box’ enter ‘Fruit’ to name this range; select B2:B9, name this range ‘Amount’.

Sum if Cell Contains Text in Another Column 14      Sum if Cell Contains Text in Another Column 15

So enter =SUMIFS(Amount,Fruit,”*”&D2&”*”) instead of former formula, we still get proper result.

Sum if Cell Contains Text in Another Column 16

SUMMARY

1. SUMIFS function supports multiple groups of criteria ranges and criteria. Sum range is listed in the first position of the formula.

2. SUMIFS function supports wildcards.

3. SUMIFS function supports defined name.

 

Sidebar