Article updated on November 28, 2017

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

The criterion is in cell B19 (Pen).

Formula in cell C19:

=SUMIF(B4:H14,B19,C4:I14)

Alternative array formula in C19:

=SUM(IF(B4:F14=B19, C4:G14, 0))

### Watch a video where I explain the formula

Recommended article

Sum adjacent values using multiple lookup text values in a column

Here is a follow up to this previous post: Vlookup with 2 or more lookup criteria and return multiple matches […]

### How to enter an array formula

1. Double click on cell C19
2. Copy/Paste above formula to cell C19
3. Press and hold CTRL + SHIFT
4. Press Enter once
5. Release all keys

Your formula has now a beginning and ending curly bracket, like this:

{=SUM(IF(B4:F14=B19, C4:G14, 0)) }

Don't enter these characters yourself, they appear automatically if you followed above steps.

### Explaining formula

Step 1 - Compare cell value with cell range

B4:F14=B19

becomes

="Pen"={"Pen", 9.4, "Pen", 5.9, "Paper Clips";"Eraser", 4.6, "Eraser", 1, "Calculator";"Ruler", 0.9, "Pen", 6.7, "Scissor";"Calculator", 3.4, "Eraser", 5.1, "Ruler";"Scissor", 2.1, "Paper Clips", 1, "Calculator";"Paper Clips", 8.3, "Calculator", 9.8, "Scissor";"Calculator", 8.8, "Paper Clips", 3.6, "Pen";"Scissor", 4.3, "Calculator", 1.7, "Eraser";"Paper Clips", 1, 0, 0, "Paper Clips";0, 0, 0, 0, "Eraser";0, 0, 0, 0, "Ruler"}

and returns

{TRUE, FALSE, TRUE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, TRUE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE, TRUE;FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE}

Some of these values are separated by a comma, some by a semicolon. A comma is a row delimiting character and a semicolon is a column delimiting character.

Learn the basics of Excel arrays

Array formulas allows you to do advanced calculations not possible with regular formulas.

Step 2 - Replace matching value with adjacent number

IF(B4:F14=B19, C4:G14, 0)

becomes

IF({TRUE, FALSE, TRUE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, TRUE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE, TRUE;FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE}, {9.4, "Pen", 5.9, "Paper Clips", 5.8;4.6, "Eraser", 1, "Calculator", 3.2;0.9, "Pen", 6.7, "Scissor", 2;3.4, "Eraser", 5.1, "Ruler", 1.9;2.1, "Paper Clips", 1, "Calculator", 6.9;8.3, "Calculator", 9.8, "Scissor", 6.5;8.8, "Paper Clips", 3.6, "Pen", 5.2;4.3, "Calculator", 1.7, "Eraser", 7.9;1, 0, 0, "Paper Clips", 1.3;0, 0, 0, "Eraser", 8.9;0, 0, 0, "Ruler", 7.4}, 0)

and returns

{9.4,0,5.9, 0,0;0,0,0, 0,0;0,0,6.7,0, 0;0,0,0,0,0;0,0, 0,0,0;0,0,0, 0,0;0,0,0,0,5.2;0, 0,0,0,0;0,0,0, 0,0;0,0,0,0, 0;0,0,0,0,0}

Recommended article

How to use the IF function

Checks if a logical expression is met. Returns a specific value if TRUE and another specific value if FALSE.

Step 3 - Sum array

SUM(IF(B4:F14=B19, C4:G14, 0))

becomes

SUM({9.4,0,5.9, 0,0;0,0,0, 0,0;0,0,6.7,0, 0;0,0,0,0,0;0,0, 0,0,0;0,0,0, 0,0;0,0,0,0,5.2;0, 0,0,0,0;0,0,0, 0,0;0,0,0,0, 0;0,0,0,0,0})

and returns \$27.20 in cell C19.

Recommended article

How to use the SUM function

The SUM function in Excel allows you to add values, the function returns the sum in the cell it is […]