Conditional sum

[oxygn]

Hi All,

I need help with an excel sheet where I have to conditional sum.

I have data in 6 columns like this:

Col A	Col B	Col C	Col D	Col E	Col F
1	25-Jan-11	1RENTAL	4	1881.6	2069.76
2	25-Feb-11	1RENTAL	4	1881.6	2069.76
2	25-Feb-11	2RENTAL	4	1881.6	2069.76
2	25-Feb-11	2RENTAL	4	1881.6	2069.76
3	25-Mar-11	1RENTAL	4	1881.6	2069.76

<tbody>
</tbody>

I want to do a conditional sum where we look for unique combination of Col A, B and C and do the sum of values for Col D, E and F
The end result should look something like this:

Col A	Col B	Col C	Col D	Col E	Col F
1	25-Jan-11	1RENTAL	4	1881.6	2069.76
2	25-Feb-11	1RENTAL	4	1881.6	2069.76
2	25-Feb-11	2RENTAL	8	3763.2	4139.52
3	25-Mar-11	1RENTAL	4	1881.6	2069.76

<tbody>
</tbody>

I have highlighted an example in bold. The unique combination of Col A:2, Col B: 25-Feb-11 and Col C: 2Rental will get the appropriate result.

Thanks for your help.

 

[Peter_SSs]

Glad to have helped.

However, I have since realised that I omitted one of the changes required if using Excel pre 2007 version. So for completeness, as well as changing the blue line as indicated previously, this line

Const f As String = "=SUMIFS(D$2:D$#,$A$2:$A$#,$H2,$B$2:$B$#,$I2,$C$2:$C$#,$J2)"

would need to be changed to

Const f As String = "=SUMPRODUCT(--($A$2:$A$#=$H2),--($B$2:$B$#=$I2),--($C$2:$C$#=$J2),D$2:D$#)"