Excel 2008 : DSUM or SUBTOTAL with Dynamic Criteria from a Large List

Please see my attached sample Truck Loading Log.

Under "Commodity" the user can choose from a drop down list of many different commodities. For each commodity, the user enters a Gross weight and a Tare weight, which calculates the Net weight. The user then chooses in what form the commodity was loaded on the truck (i.e. as a Bale, in a Gaylord (Box), or on a Pallet).

I already have the grand totals of everything, and a calculated breakdown of how many total bales, gaylords and pallets were loaded, along with how many units (combining bales, gaylords and pallets) were loaded in total.

What I would like to do is have the subtotals of each commodity be listed in the area above the grand totals. In other words, I'd like to get the gross, tare, and net weights of each commodity, as well as how many units of each commodity were loaded, and whether those units were bales, gaylords or pallets. I'd like this to be updating in real time, too, so as the user chooses a commodity it's added automatically. As a side question: Is this possible to do without sorting the list, and even if there are some blank rows?

Hopefully this explanation makes sense. Any help on this would be greatly appreciated.

Kindest Regards.

Could you save your sample as an xls (Excel 2003) file and attach it?
I don't think Excel version is essential for this task and I would like to make shot at it.

Have fun
Richard

Thank you for wanting to give it try! I just updated the file, and attached a both a .xlsx version as well as a .xls version. It looked like there may have been some compatibility issues when saving as .xls, but let me know if you can't get it open.

The reason for the 8 rows is that for any given truck load, there will most-likely only be 8 different commodities that will be loaded at most. 10 rows would be fine as well, just to make head room.

I'm looking to have the commodities to be listed dynamically as they are filled in to the left, so every time a new commodity is chosen in the "Commodity" column, the subtotal cells know to add a new commodity to the subtotal list, and to keep track of its Gross, Tare & Net weights, along with how many Bales, Gaylords & Pallets have been loaded for only that commodity.

Note: The user chooses each commodity from a drop down list of a large amount of commodities.

Hopefully this helps clarify things a little. Thank you for any help you can give!

Wow, looks amazing, wonderful job! How did you do that so quickly??? Looks like I have a lot to learn yet with Excel, lol. It looks like you found a way to do it without VBA too.

One thing I noticed: It looks like the user must have a space between each commodity in order for the subtotals to work correctly. Is there a way to make it so the user can add each commodity with or without spaces between each one?

Thank you again, much appreciated!

Amazing, thank you! Many of the functions in excel are still fairly new for me, so seeing them in action like this will help me understand their full use.