SUMIF Problems (Special Case?)

[sungersoo]

Hello,

I am trying to run a sum of "Net Cash" to "Dupe Sum". The criteria are that if the client ID and time of day are the same, sum the net cash in to "Dupe Sum"...

Currently I am using this (=IF(AA2<>"",SUMIFS(AE:AE,F:F,F2,A:A,A2,AD:AD,AD2),"")) but it is also counting transactions that happens earlier or later in the day.

Is there any way for me to instead sum the current line and the lines after that show blanks.
i.e. Parks has a sale of 59.40 at 16:29 but show a total of 131.04 in "Dupe Sum" because it is totaling 59.4+35.88+23.88+11.88)

A B F AA AB AC AD AE
[TABLE="width: 792"]
<colgroup><col span="8"></colgroup><tbody>[TR]
[TD]UserName[/TD]
[TD]Purchase Date[/TD]
[TD]Client ID[/TD]
[TD]Dupe Check[/TD]
[TD]Dupe Sum[/TD]
[TD]BH Dupe[/TD]
[TD]Date[/TD]
[TD]Net Cash[/TD]
[/TR]
[TR]
[TD]Mark[/TD]
[TD]1/1/18 14:54[/TD]
[TD]122[/TD]
[TD]Mark[/TD]
[TD]14.88[/TD]
[TD]1[/TD]
[TD]1/1/2018[/TD]
[TD]14.88[/TD]
[/TR]
[TR]
[TD]Shay[/TD]
[TD]1/1/18 16:08[/TD]
[TD]123[/TD]
[TD]Shay[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1/1/2018[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Shay[/TD]
[TD]1/1/18 16:08[/TD]
[TD]123[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1[/TD]
[TD]1/1/2018[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Parks[/TD]
[TD]1/1/18 16:29[/TD]
[TD]124[/TD]
[TD]Parks[/TD]
[TD]131.04[/TD]
[TD]1[/TD]
[TD]1/1/2018[/TD]
[TD]59.4[/TD]
[/TR]
[TR]
[TD]Parks[/TD]
[TD]1/1/18 16:29[/TD]
[TD]124[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1[/TD]
[TD]1/1/2018[/TD]
[TD]35.88[/TD]
[/TR]
[TR]
[TD]Parks[/TD]
[TD]1/1/18 16:29[/TD]
[TD]124[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1[/TD]
[TD]1/1/2018[/TD]
[TD]23.88[/TD]
[/TR]
[TR]
[TD]Parks[/TD]
[TD]1/1/18 16:30[/TD]
[TD]124[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1[/TD]
[TD]1/1/2018[/TD]
[TD]11.88[/TD]
[/TR]
</tbody>[/TABLE]


Thank you so much in advance!

 

[jtakw]

@jtakw

The breaks in column AA is something I was hoping I can work with.

Well, sort of, but the way you have your table setup makes it difficult to come up with a simpler formula, it should be a lot more efficient if we can use a Helper column, but this is what I have as it is:

Excel 2010
	A	B	F	AA	AB	AC	AD	AE
1	UserName	Purchase Date	Client ID	Dupe Check	Dupe Sum	BH Dupe	Date	Net Cash
2	Mark	1/1/2018 14:54	122	Mark	14.88	1	1/1/2018	14.88
3	Shay	1/1/2018 16:08	123	Shay	0	1	1/1/2018	0
4	Shay	1/1/2018 16:08	123			1	1/1/2018	0
5	Parks	1/1/2018 16:29	124	Parks	131.04	1	1/1/2018	59.4
6	Parks	1/1/2018 16:29	124			1	1/1/2018	35.88
7	Parks	1/1/2018 16:29	124			1	1/1/2018	23.88
8	Parks	1/1/2018 16:30	124			1	1/1/2018	11.88
9	Parks	1/1/2018 18:30	124	Parks	99.99	1	1/1/2018	99.99
10	Parks	1/1/2018 20:30	124			1	1/1/2018
11	Parks	1/1/2018 20:45	124	Parks	999.99	1	1/1/2018	999.99
Sheet2

Cell Formulas
Range	Formula
AB2	=IF(AA2<>"",SUMIFS(AE:AE,F:F,F2,A:A,A2,AD:AD,AD2)-SUMIFS(AE3:AE$2000,AA3:AA$2000,AA2,AD3:AD$2000,AD3)-SUMIFS(AB$1:AB1,AA$1:AA1,AA2,AD$1:AD1,AD1),"")

AB2 formula copied down.

 

[sungersoo]

@jtakw

I have tried that but a lot are showing negatives...

 

[jtakw]

@jtakw

I have tried that but a lot are showing negatives...

Please post some sample data where it shows negatives.