Sum(if a3=a2 and d3=d2) 2

[MrsMope]

Hi,
What I want to do is sum values in a column if the policy number ( stored in one column) and the service code (stored in another column) are the same, if not I don't want want to sum it, I want to start a new sum

IF Cell A2= a1, if c2= c1, L2= d2+d1, " "

Here is a sample of data:
a                 C      D            L (result I want)
767000510004	1608	292.78        2214.07  
767000510004	1608	1921.29      
767000510004	1609	2.05          186.34        
767000510004	1609	184.29
767000510004	1612	6606.29
767000510004	1612	2506.59
767000510006	1608	3363.15
767000510006	1608	686.89
767000510006	1608	273.83
767000510006	1608	5.12
767000510006	1608	6.37
767000510006	1608	15.4
767000510006	1608	1.12
767000510006	1608	59.32
767000510006	1609	2647.58
767000510006	1609	579.95
767000510006	1609	333.69
767000510006	1609	42.19
767000510006	1609	1.15
767000510006	1609	7.69
767000510006	1609	6.89
767000510006	1609	17.78
767000510006	1609	7.66
767000510006	1609	24.61
767000510006	1609	5.89
767000510006	1609	47.73
767000510006	1612	5092.31
767000510006	1612	307.43
767000510006	1612	25.51

Any suggestions?

 

[AnotherHiggins]

You could put this formula in column L (drag down as far as needed)
[COLOR=blue white]
=IF(AND(B1=B2,A1=A2),"",SUMPRODUCT(($A$1:$A$1000=A1)*($B$1:$B$1000=B1)*($C$1:$C$1000)))
[/color]
[attn]NOTES[/attn]:
[tab]*This will place the totals at the 'bottom' of each grouping.
[tab]*Each range in the SumProduct must be the same size.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[MrsMope]

Thank- you that was exactly what I needed!

 

[SkipVought]

Hi,

What you requested could be done with several keystrokes and NO FORUMLAS, using the Pivot Table wizard.

You need to know how to use Pivot Tables.

This result was achieved in about 5 seconds...
[tt]
Sum of D
a C Total
767000510004 1608 2214.07
1609 186.34
1612 9112.88
767000510006 1608 4411.2
1609 3722.81
1612 5425.25
Grand Total 25072.55
[/tt]


Skip,

_{[red]Be Advised![/red] A Last Will and Testament is...
A Dead Giveaway!}