0

Here is what i need to do:
enter image description here
For example i have initial data with two columns. In first column i have some values, that can repeats. At second column - values that corresponds to each value of first column.

The task is:
- Filter first column with unique values.
- Summarize relative values from the second column.

At the image i did a manual example.

Now i use the execel's filters to range alphabetically. Then manually copy only unique values from first column and manually summarize from the second. Please help how it could be easier?)

  • A Pivot Table would accomplish the same thing, though you might feel it to be overkill for your situation. – PeterT Jul 23 '18 at 0:05
1

You can use Array Formula to automate this. In this example sample input data is in cells A1:B7.

Now in Column D we shall get unique values from A and in E we shall get the Sum. However for the formula to work correctly you should leave one blank cell at Row 1 which is usually your header.

Getting unique values.

In D2 put the following formula and press CTRL + SHIFT + ENTER from within the formula bar to create an Array Formula. Formula shall now be enclosed in Curly Braces to indicate that it's an Array Formula and drag it down until you get blanks. I assume you have Excel 2007 above that supports IFERROR function. Else just remove the outer IFERROR.

=IFERROR(INDEX($A$1:$A$7,MATCH(0,COUNTIF($D$1:D1,$A$1:$A$7),0)),"")

See the below screenshot.

enter image description here

Now in Cell E2 put the following Array Formula and drag it down.

=SUM(IF($A$1:$A$7=D2,$B$1:$B$7,0))

See the below screenshot.

enter image description here

Adjust the cell references to accommodate larger table size so that you don't have to manually update the formula in the long run.

1

Create a Pivot Table for both column, Drag Task in the Row Labels and similarly drag your data to values and select sum.

  • Welcome to superuser: This may provide an answer to the question. This is the same as a comment under the question, If you can provide more detail on how to complete task making it your own detailed answer this would be more useful. In the mean time please take a couple of minutes and read:- help center .Answering: How to Answer, again welcome to superuser.Thankyou – mic84 Jul 23 '18 at 9:10
  • This works too, but a bit longer. Thanks anyway) – Nik Kov Jul 23 '18 at 21:27

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.