Calculate the first four cells based on condition

[addy_89]

A	B	C	D
x	16-03-2014	2
x	16-03-2014	3
x	16-03-2014	4
x	16-03-2014	5
x	16-03-2014	6
x	17-03-2014	7
x	17-03-2014	8
x	17-03-2014	9
x	17-03-2014	10
y	17-03-2014	1

<colgroup><col><col><col span="2"></colgroup><tbody>
</tbody>

In the above example, i want to sum the first four cells where data in column a and column b is matching and after summing the first four values, i want excel to sum the rest of the values for the matching data in column in a and b which not necessary would be 4, but will be less. I have the written the array formula for this using count if and sumifs but that is giving me the sum of total values.

{=IF(COUNTIFS($A$2:$A3,A3,$B$2:$B3,B3)>1,"",SUMIFS(C:C,A:A,A3,B:B,B3))}

Is it possible? Please help, i'm stuck

 

[jasonb75]

Not sure why I thought that would work, definitely not one of my better ideas.

Based on your example being in columns A:F

=IF(ISNUMBER(E2),SUM(D2:INDEX(D2:D5,MATCH(40000,SUMIFS(OFFSET(C2,,,{1,2,3,4}),OFFSET(A2,,,{1,2,3,4}),A2,OFFSET(B2,,,{1,2,3,4}),B2)))),"")

I haven't tested this, you might need to confirm it as an array for it to work.

 

[addy_89]

Not sure why I thought that would work, definitely not one of my better ideas.

Based on your example being in columns A:F

=IF(ISNUMBER(E2),SUM(D2:INDEX(D2:D5,MATCH(40000,SUMIFS(OFFSET(C2,,,{1,2,3,4}),OFFSET(A2,,,{1,2,3,4}),A2,OFFSET(B2,,,{1,2,3,4}),B2)))),"")

I haven't tested this, you might need to confirm it as an array for it to work.

Still not working, it is giving the sum of first 4 values of column D in F

 

[jasonb75]

Check your data then, I've just tried it with your most recent example and it works perfectly.