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]

I've tried a couple of things to do this with a formula, but so far it is not working.

To be honest, I don't think that it is possible, at least not with a formula, or if it is, it's beyond my level of knowledge.

 

[addy_89]

I've tried a couple of things to do this with a formula, but so far it is not working.

To be honest, I don't think that it is possible, at least not with a formula, or if it is, it's beyond my level of knowledge.

Thanks for the help though Please let me know if anything is possible.

 

[addy_89]

Could VBA be a possible solution?

 

[jasonb75]

Possibly, but for me writing it would be more trial and error than knowledge.

 

[addy_89]

Possibly, but for me writing it would be more trial and error than knowledge.

Okay. Than I need to do it manually .

 

[jasonb75]

Best I can do, it works, but slow to calculate.

=IF(SUM(D$1:D1)>SUM(C$1:C1),"",LOOKUP(40000,SUMIFS(OFFSET(C2,,,{1,2,3,4}),OFFSET(A2,,,{1,2,3,4}),A2,OFFSET(B2,,,{1,2,3,4}),B2)))

 

[addy_89]

Best I can do, it works, but slow to calculate.

=IF(SUM(D$1:D1)>SUM(C$1:C1),"",LOOKUP(40000,SUMIFS(OFFSET(C2,,,{1,2,3,4}),OFFSET(A2,,,{1,2,3,4}),A2,OFFSET(B2,,,{1,2,3,4}),B2)))

AWESOME!!..That worked!!...You 're the man :D

 

[addy_89]

AWESOME!!..That worked!!...You 're the man :D

One more thing and probably the last one, can i sum the values on another coloum based on the results for above formula? like

a	b	40000	4	40000	4
a	b	36000	5	36000	5
a	b	5000	6	5900	30
a	b	500	7	Blank	Blank
a	b	400	8	Blank	Blank
a	b	300	9	Blank	Blank
c	d	40000	10	40000	10
c	d	36000	11	36000	11
c	d	6000	12	6900	39
c	d	500	13	Blank	Blank
c	d	400	14	Blank	Blank

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

 

[jasonb75]

Use the same formula, just change the columns to reflect the new ranges.

 

[addy_89]

Use the same formula, just change the columns to reflect the new ranges.

That is bit Woking, when ever the formula is checking the condition of 40k it sums the value
. Please help