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

BbdHome2012

New Member
Joined
Aug 11, 2012
Messages
49
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
 
Last edited:

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
THE PROBLEM!!!

This is what I have so far:

https://app.box.com/files/0/f/0/1/f_30817720927

for some reason box.com isn't showing the times in the same format i have it create on excel but it's there when you download it.

The problem i'm having is, when i add the dates and time, (within the read highlighted box) it doesn't total up the correct amount of products sold within those dates and times

someone please help!
 
Last edited:
Upvote 0
BbdHome,

I cannot download your file from Box.
However, should your formula read greater than or equal StartTime?

Rich (BB code):
=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))

Or if you use * rather than -- then
Rich (BB code):
=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 that helps.
 
Upvote 0
That link is fine.

Here is a sample using both formulas.
I can see nothing wrong with that result.
Please indicate what is wrong for you.

Excel 2007
GHIJ
1STARTEND
2TimeDateTimeDate
300:01:1724/05/201510:31:3431/05/2015
4
5
6Barney00
7Bob The Builder00
8Dora The Explorer00
9Frozen1717
10Madagascar00
11Mike The Knight00
12Monsters Inc3838
13Peppa Pig117117
14Sonic The Hedgehog00
15Toy Story33
16Wallace and Gromit2020
Sheet1
Cell Formulas
RangeFormula
H6=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))
J6=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))
Named Ranges
NameRefers ToCells
EndDate=Sheet1!$J$3
EndTime=Sheet1!$I$3
StartDate=Sheet1!$H$3
StartTime=Sheet1!$G$3
 
Upvote 0
Take for example the times and dates i inputted looking at 1 item e.g. Peppa Big, it says 117 sold between those dates with times, but if you add it up in your head that 117 figure is wrong, it should be 121. Becuase that item sold 121 times on 1 day then 4 times on another day.

None of the figures are added up the total correctly.
 
Upvote 0
Because you have separate columns for date and time, your original formula is adding Qty between dates but only if the time is between the Start and End times on any given day.
Thus e.g. the qty 4 for Peppa Pig is inside the date range but outside the 'daily' time range.
I took this to be what you intended.

OI now assume that you want the sales between start time on the first day and end time on the last day?

If so then try.....

Excel 2007
GH
6Barney5
7Bob The Builder2
8Dora The Explorer9
9Frozen23
10Madagascar8
11Mike The Knight49
12Monsters Inc131
13Peppa Pig121
14Sonic The Hedgehog14
15Toy Story18
16Wallace and Gromit26
Sheet1
Cell Formulas
RangeFormula
H6=SUMPRODUCT(($B$5:$B$39)*($C$5:$C$39+$D$5:$D$39>=StartDate+StartTime)*($C$5:$C$39+$D$5:$D$39<=EndDate+EndTime)*($A$5:$A$39=G6))
Named Ranges
NameRefers ToCells
EndDate=Sheet1!$J$3
EndTime=Sheet1!$I$3
StartDate=Sheet1!$H$3
StartTime=Sheet1!$G$3
 
Upvote 0
Snakehip, youre a ****in star :D

thats exactly as i need it.


But I think i have another problem which i might need to create a new thread for.

I added what you suggested to my sample and it worked fine.

But on my original file i used =SUM(IFERROR(BF20,0)) in both my date and time colomns to split the date and time away from a single cell which came from an xml pull, so the format might be getting in the way here, i could add another sample closer to my original if you think you could help.

if not i really appreciate you getting back to me on all counts.

thanks,


adding new sample in a few mins
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

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