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:

=SUM(IF(COUNTIF(B10:B11, B3:B7)>0, C3:C7)) + CTRL + SHIFT + ENTER

Named ranges in array formula in B14:

=SUM(IF(COUNTIF(search_tbl, INDEX(tbl, , 1, 1))>0, INDEX(tbl, , 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:

=SUMPRODUCT((B10=B3:B7)+(B11=B3:B7), C3:C7) + ENTER

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

Share and Enjoy:
  • Share/Bookmark

Related posts:

  1. Sum adjacent values from a range using multiple lookup values in excel
  2. Vlookup with 2 or more lookup criteria and return multiple matches in excel
  3. Lookup values in a range using two or more criteria and return multiple matches in excel
  4. Lookup with multiple criteria and display multiple search results using excel formula, part 4
  5. Explaining a formula: Lookup values in a range using two or more criteria and return multiple matches in excel
  6. Search for multiple text strings in multiple cells and use in data validation in excel
  7. Extract duplicate text values from a range containing both numerical and text values in excel
  8. Filter unique text values from a range containing both numerical and text values in excel
  9. Filter unique distinct values where adjacent cells contain search string in excel
  10. Lookup values in a range using two or more criteria and return multiple matches in excel, part 2