SUMIF Problems (Special Case?)

sungersoo

New Member
Joined
Nov 9, 2017
Messages
34
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!
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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)?
 
Upvote 0
@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.
 
Upvote 0
Sorry, not understanding, if everything is correct, what do need fixed?, Please explain in detail.
 
Upvote 0
@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.
 
Last edited:
Upvote 0
Hi, still not sure what you're saying, but I'm taking a guess:


Excel 2010
ABFAAABACADAE
1UserNamePurchase DateClient IDDupe CheckDupe SumBH DupeDateNet Cash
2Mark1/1/2018 14:54122Mark14.8811/1/201814.88
3Shay1/1/2018 16:08123Shay011/1/20180
4Shay1/1/2018 16:0812311/1/20180
5Parks1/1/2018 16:29124Parks119.1611/1/201859.4
6Parks1/1/2018 16:2912411/1/201835.88
7Parks1/1/2018 16:2912411/1/201823.88
8Parks1/1/2018 16:30124Parks11.8811/1/201811.88
9Parks1/1/2018 18:30124Parks99.9911/1/201899.99
Sheet2
Cell Formulas
RangeFormula
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.
 
Upvote 0
@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?
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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