CODEWITHSUNDEEP
excelif-statementsum
Al Sharp
Al Sharp

Reputation: 1

Excel: How to sum up cells that are next to cells of the same number

I have two columns of data. The first column contains categories in the form of numbers, and the second column contains data that I want to sum up. So if you see the attached picture, I'd like to sum up all the red, green and blue into a single row, because each group shares the same numbered neighbor in the left column.

I tried using SUMIF but that doesn't seem to be designed for this.

Example

Upvotes: 0

Views: 2299

Answers (2)

paddy
paddy

Reputation: 63481

You could use column C to calculate a cumulative sum, and reset whenever the value in column A changes. So, you would:

  • set C1 to =B1
  • set C2 to =IF(A1=A2, C1+B2, B2)
  • copy C2 down to the end of the range

Now you can apply conditional formatting on column C to highlight the final sum when the next cell in column A is different, or you could make column D where:

  • set D1 to =IF(A1=A2, "", C1)
  • copy D1 down to end of the range

This gives just the final sums, and empty cells for everything else. You can hide column C, since it's an intermediate result.

Upvotes: 2

Andrew Riess
Andrew Riess

Reputation: 334

You are definitely barking up the right tree with SUMIF.

Where: =SUMIF(VALUES for condition, Condition, NUMBERS to Sum)

Take a look at the attached example I put together which I believe is what you are after:

SUMIF Example

Remember to lock the appropriate cells, in my case the VALUES for condition and NUMBERS to sum so that I can drag the formula which updates the Condition cell.

Upvotes: 2

Related Questions