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]

Hi,

So what is supposed to be the correct result for Parks in AB5? Only 59.4 or 59.4+35.88+23.88 (same transaction time)?

 

[sungersoo]

@jtakw

Everything listed above are correct. The issue is that there are at times when clients call back on the same day and the formula I pasted above also accounts for those. It's basically duplicating sales. Giving me an incorrect gross amount.

 

[jtakw]

Sorry, not understanding, if everything is correct, what do need fixed?, Please explain in detail.

 

[sungersoo]

@jtakw

For instance, client 124 called in at 9am and bought two products. Those two products would be one after another because its sorted by date and time (data).

The problem is that if that same client called in two hours later. The formula I use now would falsely add both sales from 9AM and 11AM for both lines (9AM and 11AM). It would be duplicated sales sum for both lines..

Is there any way for me to just sum up cash by looking at populated fields in "Dupe Check" and the blanks below.

 

[jtakw]

Hi, still not sure what you're saying, but I'm taking a guess:

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	119.16	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	Parks	11.88	1	1/1/2018	11.88
9	Parks	1/1/2018 18:30	124	Parks	99.99	1	1/1/2018	99.99
Sheet2

Cell Formulas
Range	Formula
AB2	=IF(AA2<>"",SUMIFS(AE:AE,F:F,F2,A:A,A2,AD:AD,AD2,B:B,B2),"")

If this is Not what you mean, then Please provide additional samples/data to the table in your OP, and show what you want.

 

[sungersoo]

@jtakw

Yes I have tried that method but at times, column B are off by a minute or couple of minutes. So that cannot work 100%

 

[jtakw]

@jtakw

Yes I have tried that method but at times, column B are off by a minute or couple of minutes. So that cannot work 100%

So how do we know when it's a NEW order and NOT a continuance of the SAME order?

 

[sungersoo]

@jtakw

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

 

[snjpverma]

@jtakw

Yes I have tried that method but at times, column B are off by a minute or couple of minutes. So that cannot work 100%

Can you show us a sample data reflecting the discrepancy by said couple of minutes?