Question: How do I sum all values in a range where adjacent cell value equals a criterion?

Sum values using criterion in a range

The criterion is in cell B19 (Pen).

Array formula in C19:

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

Download excel file for this tutorial.

Sum data in a range.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

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

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