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.

x
How to Limit Data Entry in a Cell in Excel

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.

 

Related Posts

How to Sum if Equal to Many Items or A Range in Excel
How to Sum if Equal to Many Items 12

If we want to sum numbers from a range with criteria, we often select SUMIF of SUMIFS functions as the first choice to create a formula. The criteria can be a number or an array or a collection of some ...

How to Sum if Equal to X or Y in Excel
How to Sum if Equal to X or Y 19

In daily work, if we want to sum numbers from a range, and only sum the numbers which being equal to X or Y in the range, we can create a formula with Excel build-in functions to get the result. ...

How to Sum if Contains an Asterisk
How to Sum in Last N Days 14

In our daily life, we may want to sum amounts or sales for a specific period, for example in last N days. Sum numbers in Excel is easy to run, we can apply SUM function. But if we want to ...

How to Sum if Contains an Asterisk
How to Sum if Contains an Asterisk 7

To add numbers together we need to apply SUM function. And if we want to add numbers based on some conditions, we can add criteria with the help of SUMIFS function, SUMIFS can filter data with multiple criteria effectively. If ...

How to Sum in Vertical Range
How to Sum in Vertical Range 8

If we want to add numbers based on some conditions in Excel worksheet, we can add criteria with the help of SUMIFS function, SUMIFS can filter data with multiple criteria effectively. In this article, we will introduce you the method ...

How to Sum in Horizontal Range
How to Sum in Horizontal Range9

To add numbers together we need to apply SUM function. But if we want to add numbers based on some conditions, we can add criteria with the help of SUMIFS function, SUMIFS can filter data with multiple criteria effectively. In ...

How to Sum with Criteria and Or Logic in Excel
How to Sum with Criteria and Or Logic in Excel 8

To add numbers together we need to apply SUM function. And if we want to add numbers based on some conditions, we can add criteria with the help of SUMIFS function, SUMIFS can filter data with multiple criteria effectively. If ...

How to Subtotal Values for Groups and Only Keep One Subtotal for A Group in Column
How to Subtotal Values for Groups and Only Keep One Subtotal for A Group in Column14

Sometimes we may meet the case that subtotal values for different groups and only record a subtotal value for one group in a column, for other cells in this group, keep them blank. Today we will introduce you how can ...

How to Sum by Formula If Cells Are Not Blank in Criteria Range
How to Sum by Formula If Cells Are Not Blank in Criteria13

Sometimes we may meet the cases that some blank cells exist in criteria range or sum range. In most situations we will ignore them, so we need to filter data by ‘not blank/not empty’. Today we will introduce you how ...

How to Sum by Formula if Cell Ends with in Excel
How to Sum by Formula if Cell Ends with in Excel14

We have introduced the method of sum numbers based on criteria “sum if cell begins with” (you can refer to ‘How to Sum Data if Begins with in Excel’ on our website). In this article, we will show you the ...

Sidebar