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

 

[addy_89]

Could you define huge?

.25 million entries

 

[jasonb75]

Works for me, took about 20 seconds to recalculate the sheet.

 

[addy_89]

Works for me, took about 20 seconds to recalculate the sheet.

Returns with error in d3, please see the elaborative example:-

S.No	A	B	C	D
1	x	3/16/2014	2	SUMC1
2	x	3/17/2014	3	SUMC2
3	x	3/18/2014	4	SUMC3
4	x	3/19/2014	5	SUMC4
5	x	3/20/2014	6	SUMC5
6	x	3/21/2014	7	SUMC6
7	x	3/21/2014	8	BLANK
8	x	3/23/2014	9	SUMC8
9	x	3/24/2014	10	SUM(C9:12)
10	x	3/24/2014	1	BLANK
11	y	3/24/2014	2	BLANK
12	y	3/24/2014	2	BLANK
13	y	3/24/2014	2	SUMC13
14	y	3/26/2014	2	SUMC14
15	y	3/26/2014	2	BLANK

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

check

 

[jasonb75]

The formula will not work if the data starts in row 1, if you don't have headers you will need to insert an empty row at the top.

 

[addy_89]

The formula will not work if the data starts in row 1, if you don't have headers you will need to insert an empty row at the top.

It has headers,still not working

 

[jasonb75]

The only differences I can see between the formula results and your sample are S.No 9, 11 and 14, which are errors in your sample, not the formula. The dates are the same, but column A changes at S.No 11.

 

[Peter_SSs]

I have a few comments/suggestions.

That won't work,..

That implies you haven't tried it. Whilst it is possible such a statement may be correct you should remember that people here are trying to help (for no physical reward) & a summary dismissal like that is not very encouraging.

It has headers,still not working

This at least says you have tried it. However, specific example of where it fails, what result it produces & what result it should have produced would help readers understand your requirement better.

Thank you for your help jackson,

Again, given that people are trying to help, a little more care with their name wouldn't hurt.

Works for me, took about 20 seconds to recalculate the sheet.

Jason, I agree that your formulas produce the results the OP has described, as opposed to the sample provided in post #13. However, you must have a better machine than me as with 250,000 rows, Excel stopped responding and/or told me I had insufficient resources to complete the task.


The sheet below shows Jason's formulas in column D and an alternative I tried in column E. I did get a little further with my formula but the calculation still eventually stalled on a large sample. I show the example so that specific rows/values can be referred to if the results are not what is required.

Excel Workbook
	A	B	C	D	E
1				J	PS
2	x	16/03/14	7	7	7
3	x	17/03/14	5	17	17
4	x	17/03/14	6
5	x	17/03/14	3
6	x	17/03/14	3
7	x	17/03/14	7	15	15
8	x	17/03/14	1
9	x	17/03/14	7
10	x	18/03/14	7	7	7
11	x	19/03/14	1	1	1
12	x	20/03/14	4	4	4
13	x	21/03/14	8	20	20
14	x	21/03/14	8
15	x	21/03/14	4
16	x	21/03/14	9
17	x	21/03/14	6	14	14
18	x	21/03/14	8
19	x	23/03/14	1	1	1
20	x	24/03/14	9	13	13
21	x	24/03/14	4
22	y	24/03/14	5	13	13
23	y	24/03/14	7
24	y	24/03/14	1
25	y	26/03/14	6	11	11
26	y	26/03/14	5
27
Sheet3

With so many rows, I suspect any formula solution will cause significant sheet performance issues, so I offer the following macro as an alternative. For me it processed 290,000 rows in about half a second.

Sub SumInFours()
  Dim a, b
  Dim rws As Long, i As Long, k As Long, z As Long
  Dim t As Double

  a = Range("A2", Range("A" & Rows.Count).End(xlUp)).Resize(, 3).Value
  rws = UBound(a, 1)
  ReDim b(1 To rws, 1 To 1)
  k = 1
  t = a(1, 3)
  For i = 2 To rws
    If z = 3 Or a(i, 1) <> a(i - 1, 1) Or a(i, 2) <> a(i - 1, 2) Then
      b(k, 1) = t
      t = a(i, 3)
      k = i
      z = 0
    Else
      t = t + a(i, 3)
      z = z + 1
    End If
  Next i
  b(k, 1) = t
  Range("D2").Resize(rws).Value = b
End Sub

 

[jasonb75]

I have a few comments/suggestions.


That implies you haven't tried it. Whilst it is possible such a statement may be correct you should remember that people here are trying to help (for no physical reward) & a summary dismissal like that is not very encouraging.


This at least says you have tried it. However, specific example of where it fails, what result it produces & what result it should have produced would help readers understand your requirement better.


Again, given that people are trying to help, a little more care with their name wouldn't hurt.

You said what I was thinking Peter.

Jason, I agree that your formulas produce the results the OP has described, as opposed to the sample provided in post #13. However, you must have a better machine than me as with 250,000 rows, Excel stopped responding and/or told me I had insufficient resources to complete the task.

Peter, I could, and should have given a better response, but given the level of discouragement that you noted throughout the thread, I wasn't exactly motivated to do so at the time.

When the OP gave the size of the dataset a vba solution was my immediate thought, but, given the above, I chose to push my existing solution.

When I tried the formula with 250k rows, I did get the "insufficient resources" message after autofilling, however, checking the sheet the formula had filled the entire range as expected and forcing a full recalc revealed no further issues. Maybe the autofill was right on the limit of my machines capability

Hopefully one of the 3 working solutions we have provided will be acceptable for the OP.

 

[jasonb75]

Peter,

I've just tried your formula with 250k rows to compare system performance.

Autofill returned insufficient resources with only 11k rows calculated. Forced recalc I cancelled after about a minute at only 8% complete

 

[jasonb75]

Just missed the edit timeout again

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.

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.