SumIf Problem

C

carl

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.
 
D

Domenic

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!
 
R

Roger Govier

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
 
B

Bob Phillips

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)
 
R

Ron Rosenfeld

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.
Click to expand...
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
 
Top