Excel formula required - caluclating number and quantty of transactions between time periods

Jase Alexander
Jase Alexander used Ask the Experts™
on
Hi Experts

I have a daily report that shows what our warehouse team has put into stock for the previous day - on Sheet 1 is :-

Column A has the date of the transactions
Column B as the hourly bracket
Column C has the actual posting time of the transaction
Column D has the location with which they have mad the transaction
Column E has the quantity that was posted to inventory

On Sheet 2 I have put some labels in the cells such as :
Quantity replenished before 2pm
Quantity replenished after 2pm
Number of locations replenished before 2pm
Number of locations replenished after 2pm

For the above on Sheet 2 Ive tried using the data on Sheet 1 and various Count If / Sum if combinations to get the results for the four summaries above on Sheet 2, however Im having difficulty in using the time as part of the formula

I've also tried to use SUMPRODUCT / FRREQUENCY as Column D has duplicates that need to be counted as 1 fulfilled location and not counted per entry

Is there a way using the time in Column C on Sheet 1 to a) sum the quantities put away before and after 2pm and b) count the number of locations that were used before and after 2pm

Any assistance would be greatly appreciated

J
Pickface-Replenishment--3-.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Rob HensonFinance Analyst

Commented:
See attached with a possible alternative.

I have used a Pivot Table to summarise each date with a count and a sum of the qty column.

I have added a field to the source data which classifies the transaction as AM (before 2pm) or PM (2pm or later).

In addition I have converted the data source to a Table rather than a standard list/range. With a table as new data is added the scope of the table automatically grows, the pivot is linked to the table rather than a range so when the table expands the pivot will automatically include the new data when it is refreshed.
Pickface-Replenishment--3-.xlsx
Jase AlexanderCompliance Manager

Author

Commented:
Hi Rob

Thank you for this I wouldn't have thought of doing it this way

One question and something I didn't mention in my question, the locations have duplicates - If I insert the report into the table that produces the pivot, will it just count these as singular (which I would want) or will it simply count the number of entries (ie for example, if there are four rows with 01-02-c3 will the pivot just say this is 1 or will it count 4) ??

thanks
Jase
Rob HensonFinance Analyst

Commented:
Its counting and summing the number of transactions.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Rob HensonFinance Analyst

Commented:
I have updated the file adding PickBin as a row label. This now sums the qty and counts the number of transactions for that PickBin.

For example row 15 now shows, 02-26-A1       5        1        15        3

meaning that pick bin 02-26-A1 had 5 items in one transaction in the AM and 15 items in 3 transactions in the PM.
Pickface-Replenishment--3-.xlsx
Jase AlexanderCompliance Manager

Author

Commented:
Hi Rob

This is brilliant

Just one more thing - Ive just had a request to see these totals per date per hourly bracket

I can see these available

Is there a way of displaying this so it shows Hourly Bracket per date and the relevant subtotal?

Thanks
Jase
Rob HensonFinance Analyst

Commented:
Just drag the hourly bracket field into the pivot as a row field. It could be added as a column field but gets very messy then.

See updated file attached
Pickface-Replenishment--3-.xlsx
Finance Analyst
Commented:
Adding the actual hourly bracket field looks a bit messy, in this version I have instead added time as a row field and grouped it by hour.
Pickface-Replenishment--3-.xlsx
Jase AlexanderCompliance Manager

Author

Commented:
HI Rob

Thank you so much for the help

This was perfect !

Regards
Jase
Rob HensonFinance Analyst

Commented:
Glad to help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial