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

#### BbdHome2012

##### New Member
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

Bbdhome

Last edited:

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

#### BbdHome2012

##### New Member
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

Last edited:

#### Snakehips

##### Well-known Member
BbdHome,

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.

#### Snakehips

##### Well-known Member
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
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

#### BbdHome2012

##### New Member
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.

#### BbdHome2012

##### New Member
I meant to say 117+4 = 121

#### Snakehips

##### Well-known Member
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
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

#### BbdHome2012

##### New Member
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

#### Snakehips

##### Well-known Member
Post the link and i will take a look.

Replies
9
Views
237
Replies
5
Views
245
Replies
23
Views
350
Replies
3
Views
191
Replies
8
Views
179

1,196,013
Messages
6,012,857
Members
441,736
Latest member
Tkpmm

### 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?

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