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

Watch a video where I explain the formula

Recommended article

Sum adjacent values using multiple lookup text values in a column in excel

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

Comments(1) Filed in category: Excel, SUM function, Vlookup

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 more about arrays

Learn the basics of Excel arrays

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

Comments(2) Filed in category: Count values, Excel

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

IF function explained

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

Comments(9) Filed in category: Excel

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

Excel SUM function

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

Comments(0) Filed in category: Excel, SUM function

Download excel file

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

Recommended article

Sum a filtered column

To sum a column use: =SUM(A1:A20). But what if the column is filtered? As you can see , in the […]

Comments(0) Filed in category: Excel, SUM function