I want to share…. RemoveDuplicates & NameRange & SUMPRODUCT in a single example.

[James Perry]

A simple example.
A company has 12 sales depots in the township.
Sales revenue data pours in each month for all 12 depots.
So at the year end there are 144 rows of sales data with each depot name appearing 12 times.
So how do I compute the TOTAL sales for each depot ???
Here'a how....
In a new worksheet copy&paste only the required data such as…..
A1 has the label Depot_Name ( A thru L )
B1 has the label Depot_Sales ( in USD )
A2:A145 has the depot names. Cells B2: B145 contain the sales values.
Next,
Do a 'Remove Duplicates' on the depot names range.
What remans is 12 unique depot names in column A vs 145 rows of sales data in column B.
Cool.....
Next, select the 12-name range and name it depot (in the name box)
Finally, use the SUMPRODUCT function as below…..
=SUMPRODUCT((depot =A2)*(B2:B145))
where A2 represents the name of the first depot.
This will give the TOTAL SALES for the first depot.
Change A2 to A12 to see the TOTAL SALES of the 12th depot.
Note:
U may also want to use DataValidation to suit ur requirement.

Thank u.

 

[Somendra Misra]

Hi James,

It's always good to share a sample of your technique.
See the attached file with what you have written can also be achieved through Pivot Table, which are must faster than formulas for a huge data set.

Write back if I missed any thing.

Regards,