Sum/If ???? Help

[osgood82]

Hi Guys, I wonder if anyone can help me with what is likely to be a very simple formula. Please see the below extract from my worksheet, I need to show a count of the "Lives" within each "PolicyHolderName". Unfortunatley not all the names are identical as policyholders have changed spelling etc over the years within my larger sheet.

Therefore I need the formula to perform a new sum everytime a "P" appears in the "MemberType" column. I hope this makes sense and any help on this would be much appreciated. I have managed to get it working when the sequance on has one "D" between each "P" but anymore than one "D" and my formula doesnt work.


<TABLE style="WIDTH: 444pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=593><COLGROUP><COL style="WIDTH: 158pt; mso-width-source: userset; mso-width-alt: 7716" width=211><COL style="WIDTH: 120pt; mso-width-source: userset; mso-width-alt: 5851" width=160><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2157" width=59><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3620" width=99><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #bfbfbf; WIDTH: 158pt; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 height=20 width=211>PolicyHolderName</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #bfbfbf; WIDTH: 120pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 width=160>MarketingValue</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #bfbfbf; WIDTH: 44pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl70 width=59>Lives</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #bfbfbf; WIDTH: 74pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl70 width=99>MemberType</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #bfbfbf; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl70 width=64>Count</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 height=20>McLaren Construction Ltd</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68>UK New business</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71>6</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71>P</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 height=20>McLaren Construction Ltd</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68>UK New business</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71>6</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71>D</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 height=20>Invensys Saudi Arabia</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69>ME Renewal business</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72>130</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72>P</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 height=20>Invensys Saudi Arabia</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69>ME Renewal business</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72>213</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72>D</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 height=20>Powerwell Services</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68>ME Renewal business</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71>166</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71>P</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 height=20>Powerwell Services</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68>ME Renewal business</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71>319</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71>D</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 height=20>M/Y Lazy Z</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69>RoW Renewal Business</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72>16</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72>P</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 height=20>M/Y Lazy Z</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68>RoW Renewal Business</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71>16</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71>P</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 height=20>KEO International Consultants</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69>ME Renewal business</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72>158</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72>P</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 height=20>KEO International Consultants</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69>ME Renewal business</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72>223</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72>D</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 height=20>KEO International Consultants</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69>ME Renewal business</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72>54</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72>D</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72> </TD></TR></TBODY></TABLE>

 

[Dannyh1]

Hi Try

=SUMPRODUCT(($A$2:$A$1000=A2)*($D$2:$D$1000="P")*($C$2:$C$1000))

 

[Michael M]

maybe something like

=SUMPRODUCT(--(D2:D12="P"),(C2:C12))

 

[osgood82]

Thank you for your quick response, unfortunately they dont seem to arrive at the figures I need them to. Please see below for the results from the formula, against what I need the figures to come back as. Thanks Guys

<TABLE style="WIDTH: 583pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=777><COLGROUP><COL style="WIDTH: 158pt; mso-width-source: userset; mso-width-alt: 7716" width=211><COL style="WIDTH: 120pt; mso-width-source: userset; mso-width-alt: 5851" width=160><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2157" width=59><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3620" width=99><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 4461" width=122><COL style="WIDTH: 95pt; mso-width-source: userset; mso-width-alt: 4608" width=126><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #bfbfbf; WIDTH: 158pt; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 height=20 width=211>PolicyHolderName</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #bfbfbf; WIDTH: 120pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 width=160>MarketingValue</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #bfbfbf; WIDTH: 44pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl70 width=59>Lives</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #bfbfbf; WIDTH: 74pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl70 width=99>MemberType</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #bfbfbf; WIDTH: 92pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl70 width=122>Count</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #bfbfbf; WIDTH: 95pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl70 width=126>Results I need</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 height=20>McLaren Construction Ltd</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68>UK New business</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71>6</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71>P</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71>6</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 height=20>McLaren Construction Ltd</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68>UK New business</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71>6</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71>D</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71>6</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71>12</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 height=20>Invensys Saudi Arabia</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69>ME Renewal business</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72>130</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72>P</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72>130</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 height=20>Invensys Saudi Arabia</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69>ME Renewal business</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72>213</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72>D</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72>130</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72>343</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 height=20>Powerwell Services</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68>ME Renewal business</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71>166</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71>P</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71>166</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 height=20>Powerwell Services</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68>ME Renewal business</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71>319</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71>D</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71>166</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71>485</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 height=20>M/Y Lazy Z</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69>RoW Renewal Business</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72>16</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72>P</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72>32</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72>16</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 height=20>M/Y Lazy Z</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68>RoW Renewal Business</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71>16</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71>P</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71>32</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71>16</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 height=20>KEO International Consultants</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69>ME Renewal business</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72>158</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72>P</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72>158</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 height=20>KEO International Consultants</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69>ME Renewal business</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72>223</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72>D</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72>158</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 height=20>KEO International Consultants</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69>ME Renewal business</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72>54</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72>D</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72>158</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72>435</TD></TR></TBODY></TABLE><?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

 

[Dannyh1]

How about:

=IF(A2=B2,"",SUMIF(A:A,A2,C:C))

 

[osgood82]

That is almost there, thank you very much. I have highlighted the cells were I am having problems (These are were 2 "P"s appear next to one another.
<TABLE style="WIDTH: 583pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=777><COLGROUP><COL style="WIDTH: 158pt; mso-width-source: userset; mso-width-alt: 7716" width=211><COL style="WIDTH: 120pt; mso-width-source: userset; mso-width-alt: 5851" width=160><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2157" width=59><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3620" width=99><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 4461" width=122><COL style="WIDTH: 95pt; mso-width-source: userset; mso-width-alt: 4608" width=126><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #bfbfbf; WIDTH: 158pt; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 height=20 width=211>PolicyHolderName</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #bfbfbf; WIDTH: 120pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" id=td_post_2741638 class=xl67 width=160>MarketingValue</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #bfbfbf; WIDTH: 44pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl70 width=59>Lives</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #bfbfbf; WIDTH: 74pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl70 width=99>MemberType</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #bfbfbf; WIDTH: 92pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl70 width=122>Count</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #bfbfbf; WIDTH: 95pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl70 width=126>Results I need</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 height=20>McLaren Construction Ltd</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68>UK New business</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71>6</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71>P</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl74 align=right>12</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 height=20>McLaren Construction Ltd</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68>UK New business</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71>6</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71>D</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl74 align=right>12</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71>12</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 height=20>Invensys Saudi Arabia</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69>ME Renewal business</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72>130</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72>P</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl74 align=right>343</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 height=20>Invensys Saudi Arabia</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69>ME Renewal business</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72>213</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72>D</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl74 align=right>343</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72>343</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 height=20>Powerwell Services</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68>ME Renewal business</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71>166</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71>P</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl74 align=right>485</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 height=20>Powerwell Services</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68>ME Renewal business</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71>319</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71>D</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl74 align=right>485</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71>485</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 height=20>M/Y Lazy Z</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69>RoW Renewal Business</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72>16</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72>P</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: red; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl75 align=right>32</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72>16</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 height=20>M/Y Lazy Z</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68>RoW Renewal Business</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71>16</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71>P</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: red; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl75 align=right>32</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71>16</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 height=20>KEO International Consultants</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69>ME Renewal business</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72>158</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72>P</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl74 align=right>435</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 height=20>KEO International Consultants</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69>ME Renewal business</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72>223</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72>D</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl74 align=right>435</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 height=20>KEO International Consultants</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69>ME Renewal business</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72>54</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72>D</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl74 align=right>435</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72>435</TD></TR></TBODY></TABLE>

 

[Dannyh1]

Try

=IF(AND(A2=A3,D2="P",D3="P"),C2,IF(AND(A2=A1,D2="P",D1="P"),C2,IF(A2=A3,"",SUMIF(A:A,A2,C:C))))

 

[osgood82]

That is perfect, thank you very much for all your help.

 

[jasonb75]

Is this scenario possible, and if so does the formula still give the correct result?

<TABLE style="WIDTH: 319pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=424><COLGROUP><COL style="WIDTH: 133pt; mso-width-source: userset; mso-width-alt: 6473" width=177><COL style="WIDTH: 102pt; mso-width-source: userset; mso-width-alt: 4973" width=136><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1243" width=34><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2816" width=77><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #ece9d8 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #c2d69a; WIDTH: 133pt; HEIGHT: 15.75pt; BORDER-TOP: #ece9d8 1pt solid; BORDER-RIGHT: #ece9d8 1pt solid" class=xl66 height=21 width=177>KEO International Consultants</TD><TD style="BORDER-BOTTOM: #ece9d8 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; WIDTH: 102pt; BORDER-TOP: #ece9d8 1pt solid; BORDER-RIGHT: #ece9d8 1pt solid" class=xl65 width=136>ME Renewal business</TD><TD style="BORDER-BOTTOM: #ece9d8 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; WIDTH: 26pt; BORDER-TOP: #ece9d8 1pt solid; BORDER-RIGHT: #ece9d8 1pt solid" class=xl65 width=34 align=right>158</TD><TD style="BORDER-BOTTOM: #ece9d8 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: red; WIDTH: 58pt; BORDER-TOP: #ece9d8 1pt solid; BORDER-RIGHT: #ece9d8 1pt solid" class=xl70 width=77>P</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #ece9d8 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #c2d69a; WIDTH: 133pt; HEIGHT: 15.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8 1pt solid" class=xl66 height=21 width=177>KEO International Consultants</TD><TD style="BORDER-BOTTOM: #ece9d8 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; WIDTH: 102pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8 1pt solid" class=xl65 width=136>ME Renewal business</TD><TD style="BORDER-BOTTOM: #ece9d8 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; WIDTH: 26pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8 1pt solid" class=xl65 width=34 align=right>223</TD><TD style="BORDER-BOTTOM: #ece9d8 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; WIDTH: 58pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8 1pt solid" class=xl65 width=77>D</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #c2d69a; WIDTH: 133pt; HEIGHT: 15.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8 1pt solid" class=xl67 height=21 width=177>KEO International Consultants</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; WIDTH: 102pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8 1pt solid" class=xl68 width=136>ME Renewal business</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #c2d69a; WIDTH: 26pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8 1pt solid" class=xl68 width=34 align=right>54</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: red; WIDTH: 58pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8 1pt solid" class=xl69 width=77>P

</TD></TR></TBODY></TABLE>

If not try this in E2

=IF(OR(D3="P",D3=""),SUM(INDEX(C$2:C2,IF(ISNA(MATCH(1E+100,H$1:H1)),1,MATCH(1E+100,H$1:H1)),0):C2),"")

 

[snoopyhr]

here is my 2 cents on this,

Excel Workbook
	A	B	C	D	E
2	PolicyHolderName	MarketingValue	Lives	MemberType	Count
3	McLaren Construction Ltd	UK New business	6	P
4	McLaren Construction Ltd	UK New business	6	D	12
5	Invensys Saudi Arabia	ME Renewal business	130	P
6	Invensys Saudi Arabia	ME Renewal business	213	D	343
7	Powerwell Services	ME Renewal business	166	P
8	Powerwell Services	ME Renewal business	319	D	485
9	M/Y Lazy Z	RoW Renewal Business	16	P
10	M/Y Lazy Z	RoW Renewal Business	16	P	32
11	KEO International Consultants	ME Renewal business	158	P
12	KEO International Consultants	ME Renewal business	223	D
13	KEO International Consultants	ME Renewal business	54	D	435
Sheet8