Adding the total quantity of Items between suggested start Dates and Time THEN end Dates and Time.

[BbdHome2012]

Hi Mr Excel User,

Ive had this problem for a while and at 1 point seem to have it solved until it simply just stopped working(I have samples if anyone needs)

I want to workout the qty of products sold between a period of 2 times and 2 dates that I inputted in a different cell

The name of the products are listed in (column A). The Qty of each product sold on that date and time is listed in (column B). While the dates is in (Column C)(earliest date at the top) and time is in (Column D)(earliest time from that date at the top).

Once I input the start time (G3) and start date (H3) in different cells then the end time (I3) and end date (J3)

I get the total added of each of the products between the dates and times I just inputted.

I tried using =SUMPRODUCT, here is an example of string I used:

=SUMPRODUCT(($B$5:$B$39),--($C$5:$C$39>=StartDate),--($C$5:$C$39<=EndDate),--($D$5:$D$39<=StartTime),--($D$5:$D$39<=EndTime),--($A$5:$A$39=G6))

Hope someone can help me, ask me for more information if needed

Thank you inadvance,

Bbdhome

 

[BbdHome2012]

https://app.box.com/s/bvsbzjeaseo0kwnfkmtv9ca6ojrvcvnh

This should work, the xml pull is over to the right, if you want to look direct at it.

 

[BbdHome2012]

if that doesnt work this should;

https://app.box.com/s/a58mbpp39du1iphuuaxfk8r6lvxqtxu1

 

[Snakehips]

Your dates and times shown in H & I are both the same date/time values. They just appear different by virtue of formatting.
Alternatively, your Start and End dates and times are split. i.e. Integer for date and decimal fraction for time.

So with that existing setup you will need.......

Excel 2007
	A	B	C	D
1	START		END
2	Time	Date	Time	Date
3	00:36:01	31/05/2015	10:41:46	31/05/2015
4
5
6
7
8	Crystalline Carbonide		21
9	Fermionic Condensates		0
10	Fernite Carbide		32
11	Ferrogel		0
12	Fullerides		0
13	Hypersynaptic Fibers		2
sample 1

Cell Formulas
Range	Formula
C8	=SUMPRODUCT(($G$4:$G$53)*($H$4:$H$53>=StartDate+StartTime)*($H$4:$H$53<=EndDate+EndTime)*($F$4:$F$53=A8))

Named Ranges
Name	Refers To	Cells
EndDate	='sample 1'!$D$3
EndTime	='sample 1'!$C$3
StartDate	='sample 1'!$B$3
StartTime	='sample 1'!$A$3

Time for bed where I am.

 

[BbdHome2012]

MorningSnakehips

Once again, youre an absolute Gem. With 3706 count me as one of your happy customers(who doesn't pay) lol.

Really appreciate your responses and late help.

Bbdhome,

o/