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:

=SUM(IF(COUNTIF(search_tbl,B4:F14), C4:G14, 0)) + CTRL + SHIFT + ENTER

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