## Sum values in a range where adjacent cell value equals a criterion

*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:**

**Alternative array formula in C19:**

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

- Double click on cell C19
- Copy/Paste above formula to cell C19
- Press and hold CTRL + SHIFT
- Press Enter once
- 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.

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

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

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

### Download excel file

Sum data in a range.xls

(Excel 97-2003 Workbook *.xls)

Recommended article

The SUBTOTAL function lets you sum values in a cell range that have some rows hidden or filtered, the picture […]

### 19 Responses to “Sum values in a range where adjacent cell value equals a criterion”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

This doesn't appear to work if you move the query to another sheet and reference the original sheet :(

Robin,

I am sure it does, can you provide the formula?

Hi Oscar,

Hope you are doing very well.

I am having trouble with my SUM IF function and was hoping you could help me out.

Here is the equation I am using.

=SUMIF('Data Entry Table'!C3:C1000, "=A4", 'Data Entry Table'!I3:I1000)

What I am trying to do here is:

- Working in Sheet 2, I am calling upon all the values in Sheet 1 that are identical to 'A4' in Sheet 2.

- Then I am adding numbers from an adjacent column that correspond to 'A4'.

The issue I am having is, A4 can change depending on what the user enters. I want the text in A4 (the content) rather than the address.

So for example, 'Blue' is entered in Sheet 1 multiple times in a column along with other colour names. In Sheet 2, I have a macro that compiles all the unique values... so Blue, Red, Green, etc. Now, still in Sheet 2, I want to add all the numbers in an adjacent column in Sheet 1 that are associated with a given colour and output that in Sheet 2.

I do not know what the 'colour name' will be, nor how many instances of each I will have.

I'm using colour name just as an example.

I hope I have explained this clearly.

Please let me know if you need further information from my end.

I am thinking that I cannot do what I would like with this equation, but maybe you have an alternate way of how I can achieve this?

Thank you for your time and efforts in advance.

Kindest regards,

Saad

Saad,

I am having trouble with my SUM IF function and was hoping you could help me out.Here is the equation I am using.

=SUMIF('Data Entry Table'!C3:C1000, "=A4", 'Data Entry Table'!I3:I1000)

What I am trying to do here is:

- Working in Sheet 2, I am calling upon all the values in Sheet 1 that are identical to 'A4' in Sheet 2.

- Then I am adding numbers from an adjacent column that correspond to 'A4'.

The issue I am having is, A4 can change depending on what the user enters. I want the text in A4 (the content) rather than the address.

I am not sure I understand. Is A4 a cell reference or a value?

So for example, 'Blue' is entered in Sheet 1 multiple times in a column along with other colour names. In Sheet 2, I have a macro that compiles all the unique values... so Blue, Red, Green, etc. Now, still in Sheet 2, I want to add all the numbers in an adjacent column in Sheet 1 that are associated with a given colour and output that in Sheet 2.I do not know what the 'colour name' will be, nor how many instances of each I will have.

I'm using colour name just as an example.

Is this what you are looking for?

Thankyou thankyou thankyou! :) This works brilliantly and the way you explained it made it really easy to implement!

Orion,

Thank you for commenting!

Hi Oscar,

I am trying to figure out how to create a formula and I was wondering if you could help.

I have one column with values (check amounts received) and the column next to it has the date those checks were received. For quarter end purposes, I want to add the values of checks up only if they were received after a certain date to get my accounts receivable number at quarter end. For example I have 10 checks for $1,000 each, but only 4 were received before 12/31/12, my accounts receivable would need to be $6,000. Is that possible?

Thanks!

Beth,

Download excel *.xlsx file

Beth2.xlsx

Hello Oscar,

Hope you are doing very well!

I apologize for such a delayed response to your posting - I had not realized that you had already responded a long time ago (I clicked the 'Notify me of followup comments via e-mail' but unfortunately did not receive notification).

Nevertheless, I wanted to sincerely thank you for your response and let you know that it was extremely helpful and I was able to solve my problem based on your input! The problem with my formula was that I had put quotations around =A4. Once I saw that you had not included quotations, I made the change and it worked!

Honestly, I really appreciate you taking out the time. It is comforting to know that there are people like you that are kind enough to spend their precious time helping others.

Thank you so much, Oscar!

I wish you all the best!

Kindest regards,

Saad

maybe you can help me been stuck in a rut for a few days now. essentially what im trying to do is have excel take the sum across a certain cell in the entire workbook if on a certain worksheet a value is entered into a certain cell. i hope i have explained this where you can comprehend. im fairly new to working with excel so its a little hard to explain.

Hi Oscar,

In column A, I have a long random list of two variables, "N/A" and the value 1. In column B I want to identify the number of contiguous occurrences of the value 1 before the next appearance of "N/A". I wonder if you could point me to the best way of achieving this please? Example below shows what I am after.

Much appreciation for your excellent website,

David

Col A Col B

N/A

N/A

1

1

1

1

1 5

N/A

1

1

1

1 4

N/A

N/A

1

1 2

N/A

N/A

N/A

1 1

In the example above, the space between the column of data in col A and the desired results, viz the 5, 4, 2 and 1 in col B got lost. Hope you can see what I'm after. Thanks.

hi guys,

i downloaded the sample excel sheet.why do i get the #value! error after i double clicked the c19 cell and pressed enter?

OSCAR,

YOU ARE A BRILLIANT MIND JUST HAD TO COMMENT THANKS ALOT. YOU DONT KNOW HOW MUCH HEAD ACHE I HAVE GONE THROUGH.

CHEERS!!

Hello Oscar,

I have data in multiple columns and I'm trying to create an equation that will sum the numbers in one column if the conditions in two other columns are met. Example:

In column A, there are different names, in column B there are other names, and in column C there are numbers. I want to add the numbers in column C only if the name in column A matches the name in column B. If A=B then SumC.

Column A Column B Column C

Full Name 1 Break 1124

Full Name 2 Home 3698

Full Name 3 Break 2136

In the above example. I want to add the values in Column C for Full Name 1 and "Break" in Column B. So the result should be 3260.

This is great. Thanks!

Hi Oscar,

I am from Supply chain, and read outstanding comments about you. Can you please help.

* I am implementing KPIs and need to check the container optimization.

* Check for specific region only

* Every SKUs has different no.of cases /pallets

* 10 or 12 or 20 or 25 pallets makes 1 container

* Also one sales order can have single or multiple SKUs and that too same or different.

Thanx in Advance :)

Hi Oscar

Could you please help me on this.

Thanks

abc

Use the web form on this page to contact me. Please describe in greater detail.