Dear All,
I have one big table on Sheet1. I would like to pull data from there to Sheet2!D5:D27 based on multiple criteria in Sheet2!B1:B3;B5:B27 (Person, From and To have to be exact match; although each of them can be repeated multiple times, their combination is repeated only once). Which formula I have to copy/paste down in cells D5:D27 to get the results as shown in this example?
Thank you in advance
I have one big table on Sheet1. I would like to pull data from there to Sheet2!D5:D27 based on multiple criteria in Sheet2!B1:B3;B5:B27 (Person, From and To have to be exact match; although each of them can be repeated multiple times, their combination is repeated only once). Which formula I have to copy/paste down in cells D5:D27 to get the results as shown in this example?
Excel Workbook | |||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | |||
1 | Person | Glenn | Jack | John | Ross | Melinda | Ralph | Ed | Melinda | John | Jack | Jack | |||
2 | From | 01.02.95. | 01.02.95. | 01.02.95. | 01.02.95. | 01.02.95. | 01.02.95. | 02.04.97. | 01.03.95. | 01.01.96. | 01.01.96. | 01.01.97. | |||
3 | To | 31.12.95. | 31.12.95. | 31.12.95. | 31.12.95. | 31.12.95. | 31.12.95. | 18.09.98. | 31.11.95 | 31.12.96. | 31.12.96. | 30.06.97. | |||
4 | SUM 1 | SUM | 60 | 368 | 144 | 45 | 108 | 23 | 249 | 17 | 8 | 8 | 5 | ||
5 | SUM 2 | SUM | 492 | 269 | 32 | 137 | 544 | 8 | 803 | 11 | 15 | 40 | 17 | ||
6 | SUM 3 | SUM | 237 | 682 | 64 | 23 | 163 | 31 | 404 | 15 | 10 | 5 | 6 | ||
7 | SUM 4 | SUM | 661 | 888 | 215 | 70 | 571 | 46 | 104 | 9 | 1 | 14 | 13 | ||
8 | SUM 5 | SUM | 399 | 492 | 44 | 54 | 134 | 7 | 54 | 8 | 8 | 32 | 19 | ||
9 | SUM 6 | SUM | 87 | 68 | 91 | 25 | 30 | 33 | 458 | 1 | 9 | 9 | 10 | ||
10 | SUM 7 | SUM | 607 | 389 | 76 | 32 | 399 | 52 | 4 | 13 | 17 | 4 | 22 | ||
11 | SUM 8 | SUM | 355 | 123 | 324 | 13 | 12 | 1 | 25 | 124 | 36 | 4 | 21 | ||
12 | SUM 9 | SUM | 78 | 115 | 42 | 32 | 61 | 48 | 716 | 2 | 1 | 4 | 1 | ||
13 | 1 | 1 | 20 | 13 | 1 | 1 | 17 | 4 | 5 | 1 | 0 | 1 | 1 | ||
14 | 1 | 2 | 11 | 5 | 5 | 1 | 3 | 1 | 22 | 1 | 0 | 1 | 0 | ||
15 | 1 | 3 | 1 | 14 | 3 | 1 | 9 | 4 | 18 | 1 | 0 | 1 | 1 | ||
16 | 1 | 4 | 21 | 5 | 3 | 1 | 11 | 6 | 28 | 0 | 0 | 2 | 1 | ||
17 | 1 | 5 | 7 | 18 | 2 | 1 | 11 | 3 | 23 | 0 | 0 | 1 | 1 | ||
18 | 1 | 6 | 22 | 16 | 3 | 3 | 8 | 1 | 34 | 0 | 1 | 2 | 0 | ||
Sheet1 |
Excel Workbook | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | Person | Jack | ||||
2 | From | 1.1.1997 | ||||
3 | To | 30.6.1997 | ||||
4 | Group | Concatenate | Value | |||
5 | 3 | SUM 3 | 6 | |||
6 | 4 | SUM 4 | 13 | |||
7 | 5 | SUM 5 | 19 | |||
8 | 6 | SUM 6 | 10 | |||
9 | 7 | SUM 7 | 22 | |||
10 | 1 | SUM 1 | 5 | |||
11 | 2 | SUM 2 | 17 | |||
12 | 3 | SUM 3 | 6 | |||
13 | 4 | SUM 4 | 13 | |||
14 | 5 | SUM 5 | 19 | |||
15 | 6 | SUM 6 | 10 | |||
16 | 7 | SUM 7 | 22 | |||
17 | 1 | SUM 1 | 5 | |||
18 | 2 | SUM 2 | 17 | |||
19 | 3 | SUM 3 | 6 | |||
20 | 4 | SUM 4 | 13 | |||
21 | 5 | SUM 5 | 19 | |||
22 | 6 | SUM 6 | 10 | |||
23 | 7 | SUM 7 | 22 | |||
24 | 1 | SUM 1 | 5 | |||
25 | 2 | SUM 2 | 17 | |||
26 | 9 | SUM 9 | 1 | |||
27 | 9 | SUM 9 | 1 | |||
Sheet2 |
Thank you in advance