Hi,
I have a spreadsheet which contains information for certain sales. Below is a basic representation of my dataset (the actual spreadsheet contains more columns of information and has new entries added daily from multiple people)
'Sales Data' worksheet
<tbody>
</tbody>
I would like to know if i could create a formula on another worksheet that will provide me with the product codes and total quantities for a specific date or date range. for example if I'm interested in the 10th October i can insert those date and it will return the following
'Data summary' worksheet
<tbody>
</tbody>
Product code SKMDRT appears once with the combined qty for the 2 sales on that date. Or if I'm interested in looking at data form the 10th - 11th it returns
<tbody>
</tbody>
I know the simplest way is to apply a filter to my dataset however i want to avoid this as the sales data has alot of information and is continuously updated, this is why i would like to create a separate sheet which can provide me with a summary for specific dates without affecting the main data. I have also tried a pivot table but that doesn't seem to work for what i am after. i have been reading up about creating an array formula with index/match but have had no luck applying to my data set.
any help would be much appreciated,
Thankyou.
I have a spreadsheet which contains information for certain sales. Below is a basic representation of my dataset (the actual spreadsheet contains more columns of information and has new entries added daily from multiple people)
'Sales Data' worksheet
Date | ORDER # | Product Code | Qty |
10-Oct | 102 | SKMDRT | 5 |
10-Oct | 103 | IMRDDT | 10 |
10-Oct | 104 | PMYTUV | 7 |
10-Oct | 105 | SKMDRT | 3 |
11-Oct | 106 | PDYHTW | 4 |
11-Oct | 107 | FTRHYGQ | 15 |
11-Oct | 108 | PIMHKLO | 5 |
11-Oct | 109 | PMYTUV | 12 |
12-Oct | 110 | IMRDDT | 3 |
12-Oct | 111 | KJULOIR | 2 |
12-Oct | 112 | LKJULOIR | 2 |
12-Oct | 113 | LKJUTYV | 9 |
<tbody>
</tbody>
I would like to know if i could create a formula on another worksheet that will provide me with the product codes and total quantities for a specific date or date range. for example if I'm interested in the 10th October i can insert those date and it will return the following
'Data summary' worksheet
Date | Product Code | qty |
10-Oct | SKMDRT | 8 |
10-Oct | IMRDDT | 10 |
10-Oct | PMYTUV | 7 |
<tbody>
</tbody>
Product code SKMDRT appears once with the combined qty for the 2 sales on that date. Or if I'm interested in looking at data form the 10th - 11th it returns
Date | Product Code | QTY |
10-Oct | SKMDRT | 8 |
10-Oct | IMRDDT | 10 |
10-Oct | PMYTUV | 7 |
11-Oct | PDYHTW | 4 |
11-Oct | FTRHYGQ | 15 |
11-Oct | PMYTUV | 12 |
11-Oct | PIMHKLO | 5 |
<tbody>
</tbody>
I know the simplest way is to apply a filter to my dataset however i want to avoid this as the sales data has alot of information and is continuously updated, this is why i would like to create a separate sheet which can provide me with a summary for specific dates without affecting the main data. I have also tried a pivot table but that doesn't seem to work for what i am after. i have been reading up about creating an array formula with index/match but have had no luck applying to my data set.
any help would be much appreciated,
Thankyou.