Hi all



I have a long list of items with a dollar amount. Many of the items has the same number and I want to get a total of each group. I have tried a few formulas but they dont seem to match. Please see the attachment to see what I am trying to do. This list is extremely long and I would rather not have to do this manually.

Thanks

Attached Files

Book2.xlsx (9.7 KB, 5 views)

The formula you need to enter into cell D2 and then copy down is like (You have to edit the ranges when you have more or less rows in table. And be sure you have proper absolute/relative references used.):
The formula returns group total for every member row of group. It is possible to modify it so so group total is returned for only one row in every group, but instead I advice to have a sheet where all [BU ID]'s are registered, and add there a column with my formula. An added bonus is, you can use this table as source for Data Validation List in [BU ID] column in your table (by default, Data Validation List allows to enter/select only values present in it's source).

If you use defined Table instead regular one, the same formula will be something like
An added bonus using defined Tables is, the formulas are adjusting automatically whenever any rows are added/removed. The only limiting condition for making this work is, that the whole column must have this (and only this) formula!

Or a nice and simple pivot table after making an Excel Table out of your range ( I added a dates field just in case)
To update, add rows - refresh - you're OK to go

Attached Files

Book2.xlsx (15.2 KB, 2 views)

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Thanks Arvi

Thanks Peco

This worked perfectly!! Not sure why I didn't think of this. It never crossed my mind that I could do a pivot table to solve this quick and easy....