Sum values in a range where adjacent cell value equals a criterion in excel
Question: How do I sum all values in a range where adjacent cell value equals a criterion?
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
Related posts:
Extract cell values in a range using a criterion in excel
Sum adjacent values from a range using multiple lookup values in excel
Create a list of distinct values from a list where an adjacent cell value meets a criteria in excel
Create a list of duplicates where adjacent cell value meets a condition in excel
Recommended categories
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!