Calculate the first four cells based on condition

A

addy_89

New Member
Joined
Jul 9, 2014
Messages
21
ABCD
x16-03-20142
x16-03-20143
x16-03-20144
x16-03-20145
x16-03-20146
x17-03-20147
x17-03-20148
x17-03-20149
x17-03-201410
y17-03-20141

<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 :(
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
Could you post that data again and also include the expected results so that we can see what they are and where you want them. Any further clarification you can give would also help.
 
Upvote 0
If I'm following correctly, try this in D2,then fill down, does it give the correct results?

=IF(AND(A2=A3,B2=B3),IF(COUNTIFS(A$2:A2,A2,B$2:B2,B2)=4,SUMIFS(C$2:C2,A$2:A2,A2,B$2:B2,B2),""),SUMIFS(C$2:C2,A$2:A2,A2,B$2:B2,B2)-IF(COUNTIFS(A$2:A2,A2,B$2:B2,B2)>4,LOOKUP(1E+100,D$1:D1)))
 
Upvote 0
Peter_SSs said:
Could you post that data again and also include the expected results so that we can see what they are and where you want them. Any further clarification you can give would also help.
Click to expand...

Thank you sir for your response, in column a i have repetitive value and column b i have dates and in column c i have numerical values for data set which has like 20k rows now i want to sum numerical values in column c in set of 4 and if it is not a multiple of 4, then also i want to sum up, wherever distinct entry is same in column a and column b like below.

S.NoABCD
1x16-03-20142sum(c1:c4)
2x16-03-20143blank
3x16-03-20144blank
4x16-03-20145blank
5x16-03-20146sum(c5:c7)
6x16-03-20147blank
7x17-03-20148sum(c7c12)
8x17-03-20149blank
9x17-03-201410blank
10x17-03-20141blank
11y16-03-20142sum(c11:d114)
12y16-03-20142blank
13y16-03-20142blank
14y16-03-20142blank
15y16-03-20142sum(c15)

<colgroup><col span="2"><col><col span="2"></colgroup><tbody>
</tbody>
 
Upvote 0
Just to clarify a couple of points before we start, are the following scenarios possible, and if so how should they be handled?

What if there are 11 entries where columns A and B both match? Should that return a set of results based on 4, 4 and 3 or 4 and 7?

If the matching entries are not in consecutive rows, should each set be considered as different, or a continuation from the previous set?
 
Upvote 0
jasonb75 said:
Just to clarify a couple of points before we start, are the following scenarios possible, and if so how should they be handled?

What if there are 11 entries where columns A and B both match? Should that return a set of results based on 4, 4 and 3 or 4 and 7?

If the matching entries are not in consecutive rows, should each set be considered as different, or a continuation from the previous set?
Click to expand...


Thank you for your help jackson, so if there are 11 entries it should return to a set of 4,4,3 and if 10 it should return to 4,4,2. I tried using offset function as well but did not work.

I have entries in consecutive rows as i've sorted them based on the conditions. each set should be considered as different set as values will differ.
 
Upvote 0
I think this works as required, only tested with your sample data.

In D2 =SUMIFS(C2:C5,A2:A5,A2,B2:B5,B2)
In D3 and fill down =IF(OR(A2<>A3,B2<>B3,IFERROR(COUNTIF(D2:INDEX(D$1:D2,MATCH(1E+100,D$1:D2)),"")=3,0)),SUMIFS(C3:C6,A3:A6,A3,B3:B6,B3),"")
 
Upvote 0
jasonb75 said:
I think this works as required, only tested with your sample data.

In D2 =SUMIFS(C2:C5,A2:A5,A2,B2:B5,B2)
In D3 and fill down =IF(OR(A2<>A3,B2<>B3,IFERROR(COUNTIF(D2:INDEX(D$1:D2,MATCH(1E+100,D$1:D2)),"")=3,0)),SUMIFS(C3:C6,A3:A6,A3,B3:B6,B3),"")
Click to expand...


That won't work, i have huge data.
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question
Marco, IFS and XLOOKUP but certainly happy to be wrong
Replies
2
Views
130
N0t Y0urs
N
SaraWitch
  • Solved
Return first Monday in April from one date and first Monday in current year April if another is blank
2 3
Replies
28
Views
819
Dave Patton
D
K
  • Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
308
awoohaw
awoohaw
A
  • Question
Lookup value based on multiple criteria where one is searching a cell for specific text string
Replies
0
Views
255
alex0182828
A
S
  • Question
Date column not working
Replies
4
Views
482
awoohaw
awoohaw

Forum statistics

Threads
1,214,814
Messages
6,121,711
Members
449,049
Latest member
THMarana

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top