CODEWITHSUNDEEP
excelexcel-formula
gagneet
gagneet

Reputation: 37259

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

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?

Upvotes: 0

Views: 280

Answers (2)

JNevill
JNevill

Reputation: 50034

You can use sumif() to do this:

enter image description here

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

Upvotes: 1

Spencer Ogden
Spencer Ogden

Reputation: 306

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.

Upvotes: 1

Related Questions