SumIf Problem

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.

Assumptions:

A1:E10 contains your data

First row contains your headers/labels

G2:G3 contain AccountA and AccountB

H1:K1 contain BrokerA, BrokerB, BrokerC, and BrokerD

Formula:

H2, copied across and down:

=SUMIF($A$2:$A$10,$G2,INDEX($B$2:$E$10,0,MATCH(H$1,$B$1:$E$1,0)))

Hope this helps!

In article <[email protected]>,
"carl" <[email protected]> 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.

Hi Carl

One way

Assuming your data is on Sheet1, and the summary is on Sheet2.
On Sheet 2 in column A, A2 AccountA, A3 AccountB etc.
On Sheet 2 in Row 1, B1 BrokerA, C1 BrokerB etc.

In cell B2
=SUMPRODUCT(--Sheet1!$A$2:$A$1000=$A2),Sheet1!B$2:B$1000)
Copy across through C2:E2
Copy B2:E2 down for as many accounts as you have.

Change ranges to suit, but take careful not of the "$" signs.

Regards

Roger Govier


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 the Summary table

=SUMIF(Data!A:A,A2,Data!B:B)
then
=SUMIF(Data!A:A,A2,Data!C:C)
etc.

assuming that the data is on a worksheet called Data
--

HTH

RP
(remove nothere from the email address if mailing direct)


"carl" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
>

On Tue, 18 Oct 2005 08:50:06 -0700, "carl" <[email protected]>
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