## Sum adjacent values from a range using multiple lookup criteria

*Article updated on November 28, 2017*

In a previous blog post Sum adjacent values using multiple lookup text values in a column in excel I created a formula to sum specific values using two or more conditions **in a column**.

In this blog post I will create a formula to sum specific values using two or more conditions **in a range** containing more than one column.

Array formula in E18:

### Named ranges

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 adjacent values using multiple lookup values in a range.xls

(Excel 97-2003 Workbook *.xls)

### Functions in this article:

**COUNTIF(**range,criteria**)**

Counts the number of cells within a range that meet the given condition

**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

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

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 […]### 8 Responses to “Sum adjacent values from a range using multiple lookup criteria”

### Leave a Reply

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

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

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

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

VBA code

[/vb]

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

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

Help!

In Worksheet A, I have columns delineating the area of a Zone (Z1 Area, Z2 Area etc) and the State of a Zone (Z1 State, Z2 State). This is multiplied by 10 times (i.e. up to Z10 Area, Z10 State)

In the Z1, Z2 Area column, hectare values appear, in the Z1, Z2 State column, either "State 1", "State 2" or "State 3" appears.

In a linked workbook, I have three columns "State 1", "State 2" or "State 3".

In the linked workbook columns, I need to aggregate all the Z1, Z2 etc Area values IF the Z1, Z2 State values return "State 1" or "State 2" or "State 3" respectively. e.g. I need to find the total value of Z1-Z10 Area that return a "State 1" value, and the total value of Z1-Z10 Area that returns "State 2" value.

Does this make any sense? I don't know if you are able to help me, but the above formula seems to be coming close to what I need and I wondered if you had any advice! Thnaks,Fiona

Email me an excel workbook without any sensitive data and I´ll see what I can do.

hello sir

i have 2 questions:

-1-

A1=dep checks

A2=dep cash

A3=dep suit case

A4=dep prepayment

i wrote in B1=cash and in D1=countif(A1:A1,"*"&$B1$&"*")and i dragged down in order to shaow only cells that contain "cash"...all is good till now...but what to edit in the formula above if i had to add another condition like in C1=checks??

maybe {$B1$&$C1$} ???

-2-

=find("table",A1)

if the world "table" is located in D1...is it possible to replace "table" in the formula by D1??

sam,

1, Formula in cell D1: =SUM(INDEX(COUNTIF(A2,"*"&$B$1:$C$1&"*"),0,0))

2, Yes

For some reason the formula is returning all the cells in the range and it's not searching for the named table.

column a = identifier

column b = data (numbers)

E7 = named table

F8 = formula

Look at the screenshot. Where am i going wrong?

https://s27.postimg.org/5gystyutf/Untitled_picture.png

thanks in advance.

Your formula did not work for me. SUMIF worked flawsessly. I could not tell you why yours did not work. I went back and forth from your test file to mine but it refused to give me the proper results.

=SUMIF($C$3:$C$69,"vg0pavp01",$D$3:$D$69) - ignore the absolute $ entry. I was being lazy and didn't want to have to re-write the formula each time. SO i just dragged it down and changed the text search.

search C3:69 for vg0pavp01 and add adjacent values found in D3:D69

Barry,

You only have two columns in your picture, in that example I recommend using the SUMIF function.

My post describes how to sum adjacent values from more than two columns.

The reason why your formula is not working is because you did not enter the formula as an array formula.

How to enter an array formulaSelect the cell

Type the above formula

Press and hold CTRL + SHIFT simultaneously

Press Enter once

Release all keys

If you did it right, the formula is now encapsulated with curly brackets, like this: {formula}

Don´t enter these characters yourself, they show up automatically.

Date Account Amount Result

01-Mar-16 a 100 100

01-Apr-16 b 200 200

23-Dec-16 a 100 200

27-Dec-16 a 300 500

31-Dec-16 c 100 100

31-Dec-16 a 300 800

01-Jan-17 b 200 400

02-Jan-17 b 400 800

06-Jan-17 b 200 1000

10-Jan-17 a 100 900

In the last column which is the result column i want to assign a formula which will add all the values of Amount column for the corresponding account. I have already mention the result i want in last column. Wants to know the excel formula for the same.

in t