Sum of Unique values with VLOOKUP

[ravi2628]

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.

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

 

[Peter_SSs]

I note that a Microsoft 365 formula was acceptable for you in another thread, so what about these?

ravi2628_2.xlsm
	A	B	C	D	E	F	G	H
16	Employee ID	Month	Status	Amount
17	1000012	Jan	Active	33575
18	1000012	Feb	Active	33575			Total Amount	908009
19	1000012	Mar	Active	33575				908009
20	1000012	Apr	Active	33575			Excluding Resigned Employee	833883
21	1000012	May	Active	33575				833883
22	1000012	Jun	Resigned	46670
23	1000013	Jan	Active	46421
24	1000013	Feb	Active	46421
25	1000013	Mar	Active	46421
26	1000013	Apr	Active	46421
27	1000013	May	Active	46421
28	1000013	Jun	Active	46421
29	1000013	Jul	Active	46421
30	1000013	Aug	Active	46421
31	1000013	Sep	Active	46421
32	1000014	Jan	Active	26368
33	1000014	Feb	Active	26368
34	1000014	Mar	Active	26368
35	1000014	Apr	Active	26368
36	1000014	May	Active	26368
37	1000014	Jun	Resigned	27456
38	1000015	Jun	Active	39100
39	1000016	Jun	Active	41272
40	1000017	Jun	Active	36007
41	1000034	Sep	Planned	46725
Mapping Data

Cell Formulas
Range	Formula
H19	H19	=SUM(FILTER(D17:D41,ISNUMBER(MATCH(A17:A41,'Master Data'!D2:D23,0)),0))
H21	H21	=SUM(FILTER(D17:D41,ISNUMBER(MATCH(A17:A41,'Master Data'!D2:D23,0))*(C17:C41<>"Resigned"),0))

 

[ravi2628]

I note that a Microsoft 365 formula was acceptable for you in another thread, so what about these?

ravi2628_2.xlsm
	A	B	C	D	E	F	G	H
16	Employee ID	Month	Status	Amount
17	1000012	Jan	Active	33575
18	1000012	Feb	Active	33575			Total Amount	908009
19	1000012	Mar	Active	33575				908009
20	1000012	Apr	Active	33575			Excluding Resigned Employee	833883
21	1000012	May	Active	33575				833883
22	1000012	Jun	Resigned	46670
23	1000013	Jan	Active	46421
24	1000013	Feb	Active	46421
25	1000013	Mar	Active	46421
26	1000013	Apr	Active	46421
27	1000013	May	Active	46421
28	1000013	Jun	Active	46421
29	1000013	Jul	Active	46421
30	1000013	Aug	Active	46421
31	1000013	Sep	Active	46421
32	1000014	Jan	Active	26368
33	1000014	Feb	Active	26368
34	1000014	Mar	Active	26368
35	1000014	Apr	Active	26368
36	1000014	May	Active	26368
37	1000014	Jun	Resigned	27456
38	1000015	Jun	Active	39100
39	1000016	Jun	Active	41272
40	1000017	Jun	Active	36007
41	1000034	Sep	Planned	46725
Mapping Data

Cell Formulas
Range	Formula
H19	H19	=SUM(FILTER(D17:D41,ISNUMBER(MATCH(A17:A41,'Master Data'!D2:D23,0)),0))
H21	H21	=SUM(FILTER(D17:D41,ISNUMBER(MATCH(A17:A41,'Master Data'!D2:D23,0))*(C17:C41<>"Resigned"),0))

Hi peter,

good morning. iam not much expert in excel so i use basic formulas only for critical logic i refer to the forum.

Thanks for the logic.

 

[Peter_SSs]

Thanks for the logic.

You're welcome.