Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
SumIf Problem
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 , "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. |
#3
|
|||
|
|||
SumIf Problem
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. |
#4
|
|||
|
|||
SumIf Problem
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" wrote in message ... 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. |
#5
|
|||
|
|||
SumIf Problem
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
problem office assistant | Excel Discussion (Misc queries) | |||
SUMIF | Excel Worksheet Functions | |||
Slight problem automating Excel in a service | Setting up and Configuration of Excel | |||
Excel Display Problem | Excel Discussion (Misc queries) | |||
SUMIF problem | Excel Worksheet Functions |