On Tue, 18 Oct 2005 08:50:06 -0700, "carl"
wrote:

I have a data table like this:

DataTable
AccountID BrokerA BrokerB BrokerC BrokerD
AccountA 0 0 27 0
AccountB 0 5 28 0
AccountB 177 1969 1749 400
AccountA 0 10 0 0
AccountB 5 60 0 0
AccountB 96 623 875 70
AccountA 0 10 7 0
AccountB 0 483 102 0
AccountB 70 180 358 33

I am trying to create this summary table:

SummaryTable
BrokerA BrokerB BrokerC BrokerD
AccountA 0 20 34 0
AccountB 348 3320 3112 503


Where the formula in the table body is looking at the Account a summing up
all values for each broker.

I thought I could do it with a formula.

Thank you in advance.

In addition to what the others have said, you could try a Pivot Table.


=============================
AccountID BrokerA BrokerB BrokerC BrokerD
AccountA 0 20 34 0
AccountB 348 3320 3112 503
==============================

Drag Account ID to the "COLUMNS" area.
Drag each individual Broker to the DATA area.

The initial table will be reversed from what you show, with regard to Columns
and Rows; but some of the auto-formatting options will display it in the manner
you have posted..

The only problem is that a Pivot Table is not dynamic, so needs to be refreshed
when data is changed. This can be done manually, or by using an
event-triggered macro.


--ron