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

 

[Peter_SSs]

I remembered an observation from an old thread that countif(s) and sumif(s) seem to be extremely inefficient when filled over a large range using an absolute start and relative end reference.

I wasn't aware of that. Will try to remember to test that out a bit more.

With this in mind, I cannibalized your formula and came up with

=IF(MOD(COUNTIFS(INDEX(A$1:A2,MAX(ROW(D2)-4,2)):A2,A2,INDEX(B$1:B2,MAX(ROW(D2)-4,2)):B2,B2),4)=1,SUMIFS(C2:C5,A2:A5,A2,B2:B5,B2),"")

250k rows took about 2-3 seconds, and no insufficient resource failures.

I still could not successfully apply that even with 200,000 rows.


Depends what sort of approach the OP wants to take and what the computer specs are but this may be another option for formulas.
You might let me know how fast it seems on your machine for the large data sample.
I've inserted 4 new blank rows (2:5) & hidden them.

Excel Workbook
	A	B	C	D
1	A	B	C	D
6	x	16/03/14	7	7
7	x	17/03/14	5	17
8	x	17/03/14	6
9	x	17/03/14	3
10	x	17/03/14	3
11	x	17/03/14	7	15
12	x	17/03/14	1
13	x	17/03/14	7
14	x	18/03/14	7	7
15	x	19/03/14	1	1
16	x	20/03/14	4	4
17	x	21/03/14	8	29
18	x	21/03/14	8
19	x	21/03/14	4
20	x	21/03/14	9
21	x	21/03/14	6	14
22	x	21/03/14	8
23	x	23/03/14	1	1
24	x	24/03/14	9	13
25	x	24/03/14	4
26	y	24/03/14	5	13
27	y	24/03/14	7
28	y	24/03/14	1
29	y	26/03/14	6	11
30	y	26/03/14	5
31
Sheet4

 

[jasonb75]

You might let me know how fast it seems on your machine for the large data sample.

Of course Peter, I had to do this one with a vba timer, it was to fast for me to read the timestamps doing it manually.

Autofill start</SPAN>	9:27:57.310</SPAN>
End</SPAN>	9:27:59.030</SPAN>
Difference</SPAN>	0:00:01.720</SPAN>
Recalc Start</SPAN>	9:27:59.350</SPAN>
End</SPAN>	9:28:00.030</SPAN>
Difference</SPAN>	0:00:00.680</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL></COLGROUP>

I found an error in the change I made to your previous formula as well, so that is of no use. That said, the efficiency of your new formula would make that redundant anyway.

For reference I'm running I7 4500U with 8GB ram. Not sure how that compares for performance.

 

[Peter_SSs]

Jason

Thanks for providing the timing info. That sounds pretty good to me.

 

[addy_89]

I'm soory, i was travelling for last 2 days and could not reply, that formula was working but i was putting it as array and hence the error. appologies

 

[addy_89]

That helps!!! thanks a lot jackson, but i've one more question, if I want to restrict the sum of c to a prticular value, if it crosses or equal to that value than it should return to the condition like:-

a	b	40000	40000
a	b	36000	36000
a	b	5000	6200
a	b	500	Blank
a	b	400	Blank
a	b	300	Blank
c	d	40000	40000
c	d	36000	36000
c	d	6000	6000
c	d	500	30900
c	d	400	Blank
c	d	30000	Blank

<tbody>
</tbody>

Please help !

 

[jasonb75]

So you're saying no sum should be greater than 40000?

Looking at the last 4 rows, could you explain why you have 6000, 30900, Blank, Blank and not 6900, Blank, Blank, 30000?

Are you able to use a vba / macro solution? To do this with a formula on 250k rows of data you're going to need the processing power of Tianhe-2!

Any thoughts Peter?

I'll keep on thinking, maybe another idea will come to mind.

Ja(ck)son

 

[addy_89]

So you're saying no sum should be greater than 40000?

Looking at the last 4 rows, could you explain why you have 6000, 30900, Blank, Blank and not 6900, Blank, Blank, 30000?

Are you able to use a vba / macro solution? To do this with a formula on 250k rows of data you're going to need the processing power of Tianhe-2!

Any thoughts Peter?

I'll keep on thinking, maybe another idea will come to mind.

Ja(ck)son

Jackson, First of all a very thanks to you for the help, I sounded mean before, but trust me that is not the case, I was really stuck and today when I saw you post again, I got the results!!! Thanks a lot

Yes you're correct the sum should not be greater than 40000.
You're again correct for the data, that was my mistake.
VBA can work, actually, I have broken my sheet in 4 sheets so it does take the formula but is very heptic to do. Its been 2 hours now since i'm stuck witrh this 40k thing. do you have any formula?

Please help

 

[addy_89]

Looking at the last 4 rows, could you explain why you have 6000, 30900, Blank, Blank and not 6900, Blank, Blank, 30000?

I will sort my table based on values so, this won't be the case. Thanks for observing. You guys are awesome :D

 

[jasonb75]

Which formula are you using?

Go back to the top of the thread and check all of the replies, there has been a lot of activity in your absence, and a lot of very helpful ideas that you might have missed, ideas that I cannot take credit for.

 

[addy_89]

Which formula are you using?

Go back to the top of the thread and check all of the replies, there has been a lot of activity in your absence, and a lot of very helpful ideas that you might have missed, ideas that I cannot take credit for.

I saw all the discussion, I'm using the very first formula of yours.

=IF(OR(H2<>H3,I2<>I3,IFERROR(COUNTIF(X2:INDEX(X$1:X2,MATCH(1E+100,X$1:X2)),"")=3,0)),SUMIFS(W3:W6,H3:H6,H3,I3:I6,I3),"")

Index and match one, I was trying to put if condition in this formula and work, but that did not happen.