Getting Summation from a Table, with matching values from another Table in Excel

Question

I have 2 tables created in Excel, which are identical in structure and the column and row names.

The only difference is that the first table has data (for the effort in work days) in it while the second is a reference table stating which milestone each cell belongs to. A sample of these tables is:

TBL1:

|    | App1 | App2 | App3 |
| T1 |  32  |  12  |  48  |
| T2 |  40  |  16  |  30  |
| T3 |  56  |  18  |  36  |

TBL2:

|    | App1 | App2 | App3 |
| T1 |  1   |  2   |  3   |
| T2 |  2   |  1   |  2   |
| T3 |  1   |  1   |  1   |

I want to collate these values so that I get SUM of 1, 2 and 3

|    | Days Summation   |
| 1  |  =32+56+16+18+36 |
| 2  |  =40+12+30       |
| 3  |  =48             |

So basically, want to find:

IF(COL_VAL_IN_TBL2=1) THEN SUM ALL VALUES IN TBL1 CORRESPONDING TO THE ROW-COL IN RESPECTIVE

Is it possible to get a formula which I can use to do this without using something like a Pivot Table?

I totally don't get it. How are you getting =32+56+16+18+36 and those other summations? Can you explain that part better. Collate and Corresponding to the row-col isn't doing it for me. Perhaps I'm just slow this morning. — JNevill
– JNevill, Commented May 18, 2016 at 14:10
@JNevill So I have just actually summed up the corresponding cell in the Table 1 corresponding to the cell in Table 2. If Table2 contains 1 in any cell, then the corresponding cell in Table1 needs to be added as a cumulative. — gagneet
– gagneet, Commented May 18, 2016 at 14:20
Basically for any common values in Table2, I want to SUM all the corresponding cell in Table1, for which the values in Table2 are the same. Hope that makes sense? :-) — gagneet
– gagneet, Commented May 18, 2016 at 14:23
OOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOH Yea... ok I get it. Man... I was really scratching my head trying to figure that one out. — JNevill
– JNevill, Commented May 18, 2016 at 14:24

JNevill · Accepted Answer · 2016-05-18 14:29:09Z

1

You can use sumif() to do this:

Here it's just looking at table2 values and comparing them to your 1, 2, or 3 and then summing the corresponding cells from your table1

answered May 18, 2016 at 14:29

JNevill

50.6k4 gold badges46 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Spencer Ogden · Accepted Answer · 2016-05-18 14:28:59Z

1

SUMIF will do the trick if I understand correctly:

If you put 1 in A1, then 2 in A2, etc. Then enter in B1=SUMIF(TBL2Range,A1,TBL1Range) and copy down. Where TBL2Range is the address of your table.

answered May 18, 2016 at 14:28

Spencer Ogden

3061 silver badge6 bronze badges

Collectives™ on Stack Overflow

Getting Summation from a Table, with matching values from another Table in Excel

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related