Sum adjacent values from a range using multiple lookup values in excel
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
Related blog posts
- Sum adjacent values using multiple lookup text values in a column in excel
- Lookup values in a range using two or more criteria and return multiple matches in excel
- Explaining a formula: Lookup values in a range using two or more criteria and return multiple matches in excel
- Vlookup with 2 or more lookup criteria and return multiple matches in excel
- Lookup values in a range using two or more criteria and return multiple matches in excel, part 2
Welcome! I am Oscar and here to help you! Feel free to comment and ask excel questions.Don´t forget to add my
February 11th, 2010 at 6:50 am
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
February 11th, 2010 at 10:16 pm
Email me an excel workbook without any sensitive data and I´ll see what I can do.