SUMIFS exclude duplicate rows

[Talal T7]

Hello everyone,

I have the below table :

[TABLE="width: 337"]
<tbody>[TR]
[TD="align: left"]Client ID[/TD]
[TD="align: left"]Policy Number[/TD]
[TD="align: left"]Number of Employees[/TD]
[TD="align: left"]Gross[/TD]
[/TR]
[TR]
[TD]1100[/TD]
[TD]1001[/TD]
[TD]10[/TD]
[TD="align: right"]50000[/TD]
[/TR]
[TR]
[TD]1100[/TD]
[TD]1001[/TD]
[TD]10[/TD]
[TD="align: right"]50000[/TD]
[/TR]
[TR]
[TD]1100[/TD]
[TD]1001[/TD]
[TD]10[/TD]
[TD="align: right"]50000[/TD]
[/TR]
[TR]
[TD]1100[/TD]
[TD]1001[/TD]
[TD]10[/TD]
[TD="align: right"]50000[/TD]
[/TR]
[TR]
[TD]1100[/TD]
[TD]1002[/TD]
[TD]20[/TD]
[TD="align: right"]100000[/TD]
[/TR]
[TR]
[TD]1100[/TD]
[TD]1002[/TD]
[TD]20[/TD]
[TD="align: right"]100000[/TD]
[/TR]
[TR]
[TD]1100[/TD]
[TD]1003[/TD]
[TD]30[/TD]
[TD="align: right"]150000[/TD]
[/TR]
[TR]
[TD]1100[/TD]
[TD]1003[/TD]
[TD]30[/TD]
[TD="align: right"]150000[/TD]
[/TR]
[TR]
[TD]1100[/TD]
[TD]1003[/TD]
[TD]30[/TD]
[TD="align: right"]150000[/TD]
[/TR]
[TR]
[TD]2100[/TD]
[TD]2001[/TD]
[TD]10[/TD]
[TD="align: right"]50000[/TD]
[/TR]
[TR]
[TD]2100[/TD]
[TD]2001[/TD]
[TD]10[/TD]
[TD="align: right"]50000[/TD]
[/TR]
[TR]
[TD]2100[/TD]
[TD]2002[/TD]
[TD]10[/TD]
[TD="align: right"]50000[/TD]
[/TR]
[TR]
[TD]2100[/TD]
[TD]2002[/TD]
[TD]10[/TD]
[TD="align: right"]50000[/TD]
[/TR]
</tbody>[/TABLE]

and I want to know the total of employees per client code exclude duplicate policy number ( not summing number of employees under same policy number)
For example,

[TABLE="width: 255"]
<tbody>[TR]
[TD="align: left"]Client ID[/TD]
[TD="align: left"] Total Employees[/TD]
[TD="align: left"][/TD]
[/TR]
[TR]
[TD="align: right"]1100[/TD]
[TD] =10 for policy number 1001 + 20 for policy number 1002 + 30 for policy number 1003 = 60[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Thanks ,
Talal[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

Welcome to the MrExcel board!

Is this what you want?

Excel Workbook
	A	B	C	D	E	F	G
1	Client ID	Policy Number	Number of Employees	Gross		Client ID	1100
2	1100	1001	10	50000		Total	60
3	1100	1001	10	50000
4	1100	1001	10	50000
5	1100	1001	10	50000
6	1100	1002	20	100000
7	1100	1002	20	100000
8	1100	1003	30	150000
9	1100	1003	30	150000
10	1100	1003	30	150000
11	2100	2001	10	50000
12	2100	2001	10	50000
13	2100	2002	10	50000
14	2100	2002	10	50000
SUMPRODUCT