Sort by SKU, make the sum of identical SKU by a given date and time!

[A. Cousineau]

Im a rooky in VBA and excel functions, i have to do a litle Excel application that have to grab from a text file, SKUs, Units Number, Date and time of record and present only SKU from a selected Date and time given by user!

I have done almost all the things to accomplish the process but im stuck with the date and time now!

I have done the substitute

=SUBSTITUTE(N3,"-",",")

to use in a function that use the date

=SUMIFS(Q:Q,R:R,">="&DATE(16,4,4),R:R,"<="&DATE(16,4,15))


Exemple of the data table im trying to work with:
121511,650,14-04-16,18:28:48
121697,18,14-04-16,18:40:08
121720,432,14-04-16,22:34:16
121720,93,14-04-16,22:34:25
121720,314,14-04-16,22:57:26
122553,192,14-04-16,23:06:30
122553,192,14-04-16,23:09:31
122553,84,14-04-16,23:17:38
122553,95,14-04-16,23:20:06
122553,144,14-04-16,23:34:03
122553,144,14-04-16,23:34:10
122553,140,14-04-16,23:34:25
122558,192,14-04-16,23:40:53
122558,192,14-04-16,23:50:36
122558,192,14-04-16,23:55:58
122558,61,14-04-16,23:59:10
122558,23,15-04-16,0:57:31
122558,120,15-04-16,0:57:37
122554,288,15-04-16,1:01:33
122554,16,15-04-16,1:02:02
122554,78,15-04-16,1:03:56
122561,216,15-04-16,1:05:21
122561,123,15-04-16,1:05:30
122561,216,15-04-16,1:07:03
122561,54,15-04-16,1:07:11
122561,122,15-04-16,1:09:25
I replace spaces with "," comma.

Any help will be appreciated.  :)
p.s: sorry for my bad English :(

 

[Nebu]

Hi:
Assuming your data is in column A to D the formula will be like
=SUMIFS($B$1:$B$26,$A$1:$A$26,$H$1,C1:C26,">="&DATE(2016,4,14),C1:C26,"<="&DATE(2016,4,15))

Note: I have given a sample of your store keeping unit on column H1 to test the formula.

Thanks

 

[bosco_yip]

Database function is the fastest way in compare with the other Excel functions as in this example.

Setup as per file attached, criteria can be changed at your requirement.

1] Multi-lines criteria way.

In "Total" I2, formula copy down :

=DSUM(A$1:C$27,B$1,F$1:H2)-SUM(I$1:I1)

or,

2] Single-line criteria way.

In "Total" K2, formula :

=DSUM(A1:C27,B1,F1:H2)

Regards
Bosco

 

[A. Cousineau]

Thank You Nebu, you are right on!

This is the end result with my function:

=SUMIFS(B:B,A:A,H1,D:D,">="&TIME(N10,0,0),D:D,"<="&TIME(N17,0,0),C:C,">="&DATE(N6,N7,N8),C:C,"<="&DATE(N13,N14,N15))