I have a situation. I have three columns

|    A    |  B  |   C   |
|Category | Sub | Price |

Column A is basically Category column, column B is Sub Category and "C" is price.

I need to add the prices in column C if the category names and sub categories names are Same. Data structure is like below:

|A    |B      | C  |
Cat A | Sub A | 100
Cat A | Sub A | 100
Cat A | Sub B | 100
Cat A | Sub A | 100
Cat B | Sub A | 100
Cat B | Sub B | 200
Cat B | Sub B | 100

So what I need to add the values for rows with similar A and B and make it one row.

For example, in Excel find all Cat A and Sub A and make one row, add all values of these:

Cat A | Sub A | 300
Cat A | Sub B | 100
Cat B | Sub B | 300

I used the 'consolidate' option, but it's only taking one left column, so it' not preserving the column B data if I choose the data based on column A.

Please help.

  • Don't reinvent the wheel, this is exactly what pivot tables are for. – Kyle Aug 3 '15 at 21:19
  • You can use Remove Duplicates feature to get only unique records.

Select Column A i.e, category and Column B i.e, Sub-Category and use Remove Duplicates or use Unique Records feature in the Advanced filter. and paste the records some where else.

Here , Don't select Column C i.e, Price

After, You will get Two columns with unique records.

cat   sub-cat
---   -------
Cat A   Sub-cat A
Cat A   Sub-cat B
Cat B   Sub-Cat A
Cat B   Sub-Cat B

Now, you can use Sumifs formula

The second method and simple way is to use Pivot Tables as mentioned in the comments

  • thank you @harsha for your suggestion. I used Pivot table that Kyle suggested and it worked like charm. I totally forgot the pivot option :) – user3550203 Aug 4 '15 at 21:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.