summary of distinct values

hi.
i'm looking for formulas that do as following:
1. dynamically adding distinct values from a column (col D based on col A)
2. sumamrize all the values of the distinct occurrances (based on col B)
3. summarize the total values of the summarized column

all should be working dynamically.
i have something that works but it is very slow as i add values to the raw data (the recalculating process).
I'm looking for something more efficient and fast.
10x.

aa.jpg

Are you still using excel version 2016??

Note: Images are difficult to see , and also requires that I input all the data myself, which is very time consuming.

A sample sheet would help here


Please see the Yellow Banner at the top of the page explaining how to attach a sample spreadsheet

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

Here are the instructions, found at the top of the page again

Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. For a new thread (1st post), scroll to Manage Attachments, otherwise scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

i have copied into a spreadsheet - as the spreadhseet is backwards on my system - which i was not sure of, as not seen before

BUT i have used a pivot table to do the SUM and total - is that something you can use - as that will change with the data - by just by refreshing

any good

you did not answer

Are you still using excel version 2016??

as later version 365 etc have a function UNIQUE , which you could use very easily

otherwise
=IFERROR(INDEX($A$2:$A$30, MATCH(0, FREQUENCY(IF(EXACT($A$2:$A$30, TRANSPOSE($D$1:D1)), MATCH(ROW($A$2:$A$30), ROW($A$2:$A$30)), ""), MATCH(ROW($A$2:$A$30), ROW($A$2:$A$30))), 0)), "")

see attached 2 sheets
1 with a pivot table
2nd with a function