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 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
Lookup values in a range using two or more criteria and return multiple matches in excel, part 2
Vlookup with 2 or more lookup criteria and return multiple matches in excel
Recommended categories
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