## Sum adjacent values using multiple lookup text values in a column

*Article last updated on November 28, 2017*

Here is a follow up to this previous post: Vlookup with 2 or more lookup criteria and return multiple matches in excel

### Sum search criteria price values

Cell references in array formula in B14:

Named ranges in array formula in B14:

**2**, 1))) + CTRL + SHIFT + ENTER

The array formula returns a sum of values from a column you specify. The column you specify is bolded in the above array formula.

The sumproduct formula:

The sumproduct formula expands significantly if many search criteria are being used. The first formula does not change in size when additional search criteria are added.

You can also sum specific price values using pivot table.

### Named ranges

tbl (B2:C6)

search_tbl (B9:B10)

What is named ranges?

### How to increase the number of search criteria

Change the named range search_tbl.

### Download excel example file

Sum-using-two-or-more-criteria.xls

(Excel 97-2003 Workbook *.xls)

### Functions in this article:

**IF(**logical_test, [value_if_true], [value_if_false]**)
**Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

**INDEX(**array,row_num,[column_num]**)**

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

**ROW(**reference**)** Returns the row number of a reference

**SMALL(**array,k**)** Returns the k-th smallest row number in this data set.

**SEARCH()** Returns the number of the character at which a specific character or text string is first found, reading left to right (not case sensitive)

**TRANSPOSE(**array**)**

Converts a vertical range to a horizontal range, or vice versa.

**SUM(**number1,[number2],**)
**Adds all the numbers in a range of cells

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

Lookup and return multiple values concatenated into one cell

This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]

Question: How do I create a chart that dynamically adds the values, as i type them on the worksheet? Answer: […]

### One Response to “Sum adjacent values using multiple lookup text values in a column”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

Thanks, worked to perfection! Please could you explain how and why it works, as you did on the page about returning multiple values using vlookup? Appreciate your help :)