CODEWITHSUNDEEP
arraysexcel
Rob
Rob

Reputation: 11

Excel: Sum up values of a table based on a condition or grouping from another table

I could not find exactly this problem, though it seems to be similar to this one: Conditional SUM using multiple tables in EXCEL except the fact that the rows will not always have the same sequence in the tables.

My results table (RES) shall be populated based on the Groups (A, B, C) of the Group Table and the values of Value Table.

I am grouping several Products into Groups A, B, C according to the Group Table:

Products    | Group
------------------------
P1          | A
P2          | A
P3          | B
P4          | B
P5          | A
P6          | C

For several periods I have the values that these products generate in different tables. These tables have the same column structure, for example:

Value Table Period 1        #   Value Table Period 2    
Products    | Value         #   Products    | Value
------------------------    #   ------------------------
P3          | 40            #   P2          | 60
P5          | 10            #   P5          | 20
P2          | 60            #   P1          | 10
P6          | 50            #   P3          | 30
P1          | 20            #   P6          | 40
P4          | 30            #   P4          | 50

These values I am trying to consolidate into a Results table, which would sum up the different products according to their groups within the respective periods, as follows:

Result  | Period 1  | Period 2
-----------------------------------
A       | 90        | 90
B       | 70        | 80
C       | 50        | 40

I tried to apply the array concept of the other post, but I was not successful in adapting it to my tables, which differ in some structural aspects. I´d be happy if I could avoid the use of VBA.

Thanks a lot in advance!

Upvotes: 1

Views: 290

Answers (2)

Domenic
Domenic

Reputation: 8114

Here's another way...

=SUM(IF(ISNUMBER(MATCH(Period1!$A$2:$A$7,IF(Result!$I$17:$I$22=Result!$A2,Result!$H$17:$H$22),0)),Period1!$B$2:$B$7))

Using the semi-colon as a separator...

=SUM(IF(ISNUMBER(MATCH(Period1!$A$2:$A$7;IF(Result!$I$17:$I$22=Result!$A2;Result!$H$17:$H$22);0));Period1!$B$2:$B$7))

Note that the formula needs to be confirmed with CONTROL+SHIFT+ENTER.

Upvotes: 0

Rob
Rob

Reputation: 11

after some additional efforts I found the right array formula, which should be put into the B2 cell of the Result table:

=SUM(SUMIF(Period1!$A$2:$A$7;IF(Result!$I$17:$I$22=Result!$A2;Result!$H$17:$H$22);Period1!$B$2:$B$7))

confirm with CTRL+SHIFT+ENTER and copy down and across.

I hope that this helps you, too ;-)

Upvotes: 0

Related Questions