Find if a character is in a range and cell in Microsoft Excel

In this article, we will learn Find if a character is in a range and cell in Microsoft Excel.

Scenario:

In simple words, while working with data tables, sometimes we need to get the sum of the cells where ranges meet one or more text values having OR criteria. For example finding the count of values in a range matching particular ID Or finding the sum of quantity for a particular product in the table.Criteria is the value in range that can match with one or more required values given in another list.

How to solve the problem?

For this article we will be required to use the SUMPRODUCT function. Now we will make a formula out of these functions. Here we are given a dataset and a range and we need to match either of the values in range and get the sum of corresponding values from dataset.

Generic formula:

range : range to match with criteria range

cri_range : criteria range

sum_range : range where sum is required

Example :

All of these might be confusing to understand. Let's understand how to use the function using an example. Here we have Order IDs from different regions with their respective quantity and Price.

We need to get the sum of TotalPrice for a specific Product in other cell I4.

Use the formula:

Explanation:

C2:C21 : range where formula matches values.

 I4 : Value from the other cell to match with C2:C21 range

A2:A26 : sum_range where amount adds up.

=SUMIF({"Carrot";"Whole Wheat";"Chocolate Chip";"Chocolate Chip";"Arrowroot";"Carrot";"Whole Wheat";"Carrot";"Carrot";"Potato Chips";"Arrowroot";"Chocolate Chip";"Whole Wheat";"Carrot";"Carrot";"Potato Chips";"Arrowroot";"Oatmeal Raisin";"Bran";"Oatmeal Raisin"},
I4,
{58.41;303.63;108.46;153.34;82.84;95.58;520.01;90.27;177;37.8;78.48;57.97;97.72;77.88;40.71;36.45;93.74;349.32;78.54;93.72})

This formula adds up all the values corresponding to all the values in cell I4 i.e. “Oatmeal raisin

Press Enter to get the Sum

The formula returns 443.04 as the sum for the value in the other cell.

As you can see this formula returns the sum if the cell is equal to a value.

Another Example:

Here we have a dataset (A1:D50) having the order date, city, product & its quantity.

Firstly,  we need to find the sum of quantity where the product matches any of the values mentioned in the separate range. Now we will use the following formula to get the sum

Use the Formula:

E5:E11 : range in dataset which needs to be matched

product : criteria range

D2:D50 : sum range, sum of quantity

Explanation:

• SUMIF function returns the sum if it has to match one value with the range. But here SUMIF takes the argument and returns an array to SUMPRODUCT function.

=SUMPRODUCT ( { 760 ; 348 ; 499 } )

• SUMPRODUCT function gets the returned array, which has an array of sum of quantity for different products and returns the SUM of the returned array.

Here the range is given as cell reference and criteria range is given as named range. Press Enter to get the count.

As you can see in the above snapshot that sum of quantity having carrot, chocolate chip or whole wheat is 1607.

Here are some observational notes shown below.

Notes:

1. The formula only works with numbers.
2. The formula works only when there are no duplicates in the lookup table
3. The SUMPRODUCT function considers non - numeric values as 0s.
4. The SUMPRODUCT function considers logic value TRUE as 1 and False as 0.
5. The argument array must be of the same length else the function.

Hope this article about Find if a character is in a range and cell in Microsoft Excel is explanatory. Find more articles on calculating values and related Excel formulas here. If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook. We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write to us at info@exceltip.com.

Related Articles :

How to use the SUMPRODUCT function in Excel: Returns the SUM after multiplication of values in multiple arrays in excel.

SUM if date is between : Returns the SUM of values between given dates or period in excel.

Sum if date is greater than given date: Returns the SUM of values after the given date or period in excel.

2 Ways to Sum by Month in Excel: Returns the SUM of values within a given specific month in excel.

How to Sum Multiple Columns with Condition: Returns the SUM of values across multiple columns having condition in excel

How to use wildcards in excel : Count cells matching phrases using the wildcards in excel

Popular Articles :

How to use the IF Function in Excel : The IF statement in Excel checks the condition and returns a specific value if the condition is TRUE or returns another specific value if FALSE.

How to use the VLOOKUP Function in Excel : This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.

How to use the SUMIF Function in Excel : This is another dashboard essential function. This helps you sum up values on specific conditions.

How to use the COUNTIF Function in Excel : Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.