Hi Everyone,
Good Morning,Afternoon,Evening
I required a formula to sum the values using lookup. i have tried the formula but its not working so any one can suggest.
Thanks & Regards,
Ravi
Good Morning,Afternoon,Evening
I required a formula to sum the values using lookup. i have tried the formula but its not working so any one can suggest.
Example.xlsx | |||
---|---|---|---|
D | |||
1 | Employee ID | ||
2 | 1000012 | ||
3 | 1000013 | ||
4 | 1000014 | ||
5 | 1000015 | ||
6 | 1000016 | ||
7 | 1000017 | ||
8 | 1000018 | ||
9 | 1000019 | ||
10 | 1000020 | ||
11 | 1000021 | ||
12 | 1000022 | ||
13 | 1000023 | ||
14 | 1000024 | ||
15 | 1000025 | ||
16 | 1000026 | ||
17 | 1000027 | ||
18 | 1000028 | ||
19 | 1000029 | ||
20 | 1000030 | ||
21 | 1000031 | ||
22 | 1000032 | ||
23 | 1000033 | ||
Master Data |
Example.xlsx | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | |||
15 | ||||||||||
16 | Employee ID | Month | Status | Amount | Mapping | |||||
17 | 1000012 | Jan | Active | 33575 | 1000012 | |||||
18 | 1000012 | Feb | Active | 33575 | 1000012 | Total Amount | 908009 | |||
19 | 1000012 | Mar | Active | 33575 | 1000012 | #VALUE! | ||||
20 | 1000012 | Apr | Active | 33575 | 1000012 | Excluding Resigned Employee | 833883 | |||
21 | 1000012 | May | Active | 33575 | 1000012 | #VALUE! | ||||
22 | 1000012 | Jun | Resigned | 46670 | 1000012 | |||||
23 | 1000013 | Jan | Active | 46421 | 1000013 | |||||
24 | 1000013 | Feb | Active | 46421 | 1000013 | |||||
25 | 1000013 | Mar | Active | 46421 | 1000013 | |||||
26 | 1000013 | Apr | Active | 46421 | 1000013 | |||||
27 | 1000013 | May | Active | 46421 | 1000013 | |||||
28 | 1000013 | Jun | Active | 46421 | 1000013 | |||||
29 | 1000013 | Jul | Active | 46421 | 1000013 | |||||
30 | 1000013 | Aug | Active | 46421 | 1000013 | |||||
31 | 1000013 | Sep | Active | 46421 | 1000013 | |||||
32 | 1000014 | Jan | Active | 26368 | 1000014 | |||||
33 | 1000014 | Feb | Active | 26368 | 1000014 | |||||
34 | 1000014 | Mar | Active | 26368 | 1000014 | |||||
35 | 1000014 | Apr | Active | 26368 | 1000014 | |||||
36 | 1000014 | May | Active | 26368 | 1000014 | |||||
37 | 1000014 | Jun | Resigned | 27456 | 1000014 | |||||
38 | 1000015 | Jun | Active | 39100 | 1000015 | |||||
39 | 1000016 | Jun | Active | 41272 | 1000016 | |||||
40 | 1000017 | Jun | Active | 36007 | 1000017 | |||||
41 | 1000034 | Sep | Planned | 46725 | #N/A | |||||
Mapping Data |
Cell Formulas | ||
---|---|---|
Range | Formula | |
H19 | H19 | =SUM(SUMIFS(D17:D41,'Master Data'!D1:D23,'Mapping Data'!A17:A41)) |
H21 | H21 | =SUM(SUMIFS(D17:D41,'Master Data'!D1:D23,'Mapping Data'!A17:A41,'Mapping Data'!C17:C41,"Active")) |
E17:E41 | E17 | =VLOOKUP(A17,'Master Data'!$D$2:$D$23,1,0) |
Thanks & Regards,
Ravi