weekly summary sheet Excel

User avatar
luv2bounce
StarLounger
Posts: 59
Joined: 07 Dec 2010, 12:21

weekly summary sheet Excel

Post by luv2bounce »

I feel like there should be a very simple solution for this, but can't make a pivot table work and wonder if someone can help.

there is a spreadsheet that lists all 365 days where people enter items issued on a daily basis. they would like a second tab to show weekly totals to then track trends. i was trying to use sumifs - but get errors is there a simpler way?
You do not have the required permissions to view the files attached to this post.

User avatar
StuartR
Administrator
Posts: 12577
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: weekly summary sheet Excel

Post by StuartR »

I had a quick look at this workbook, but I can't see any dates, just 100 rows that list #, Item, M/F/Unisex. Minimum stock, Current Qty
StuartR


User avatar
luv2bounce
StarLounger
Posts: 59
Joined: 07 Dec 2010, 12:21

Re: weekly summary sheet Excel

Post by luv2bounce »

the dates are actually in row 4. the full dates aren't displayed. the weeks are named variables.

User avatar
StuartR
Administrator
Posts: 12577
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: weekly summary sheet Excel

Post by StuartR »

Row 4 certainly doesn't look like dates to me
You do not have the required permissions to view the files attached to this post.
StuartR


User avatar
hamster
StarLounger
Posts: 58
Joined: 10 Mar 2021, 22:57

Re: weekly summary sheet Excel

Post by hamster »

maybe something like this?
but your description is very poor and not clear
You do not have the required permissions to view the files attached to this post.

User avatar
luv2bounce
StarLounger
Posts: 59
Joined: 07 Dec 2010, 12:21

Re: weekly summary sheet Excel

Post by luv2bounce »

Sorry. I understand the issue and do apologise! i forget that people can't read my mind!!!!! On a daily basis, staff will issue clothing and update the issued spreadsheet to reflect what they have issued. On the weekly summary tab, they would like to summarise what has been issued on a weekly basis, so they can look for trends to influence future stock ordering as well as to highlight information about service users. Basically they would like a weekly summary for each of the sizes and items of clothing. the pivot table you have created works well, but they have asked for the simplified version as shown in the weekly summary tab. I hope that explains it better. I couldn't find a simplified way to put it in the pivot table.

User avatar
luv2bounce
StarLounger
Posts: 59
Joined: 07 Dec 2010, 12:21

Re: weekly summary sheet Excel

Post by luv2bounce »

StuartR wrote:
09 May 2023, 13:39
Row 4 certainly doesn't look like dates to me
Sorry - it is row 4 in the issued and weekly summary tabs which are the ones I was interested in. forgive my terrible description of my problem!

User avatar
StuartR
Administrator
Posts: 12577
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: weekly summary sheet Excel

Post by StuartR »

luv2bounce wrote:
09 May 2023, 14:54
StuartR wrote:
09 May 2023, 13:39
Row 4 certainly doesn't look like dates to me
Sorry - it is row 4 in the issued and weekly summary tabs which are the ones I was interested in. forgive my terrible description of my problem!
The workbook that I downloaded from the first post in this thread has only ONE sheet.
StuartR


User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: weekly summary sheet Excel

Post by HansV »

I think you attached the wrong workbook, there is no "issued" tab, nor a "weekly summary" tab.
Best wishes,
Hans

User avatar
hamster
StarLounger
Posts: 58
Joined: 10 Mar 2021, 22:57

Re: weekly summary sheet Excel

Post by hamster »

move FullYear to the FILTERS area and you will get possibility to choose single week table
note that this is only for one year 2023
would be nice to see example of expected result :thumbup:
You do not have the required permissions to view the files attached to this post.

User avatar
luv2bounce
StarLounger
Posts: 59
Joined: 07 Dec 2010, 12:21

Re: weekly summary sheet Excel

Post by luv2bounce »

I thought I uploaded the full worksheet - no wonder there is so much confusion! Thank you all for being so amazing in trying to assist.

User avatar
luv2bounce
StarLounger
Posts: 59
Joined: 07 Dec 2010, 12:21

Re: weekly summary sheet Excel

Post by luv2bounce »

Thank you all for your patience with me!
hamster wrote:
09 May 2023, 15:43
move FullYear to the FILTERS area and you will get possibility to choose single week table
note that this is only for one year 2023
would be nice to see example of expected result :thumbup:
I think I can make the pivot table work - how are you getting the full year? I'll also try to attach the file with 2 tabs.
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: weekly summary sheet Excel

Post by HansV »

Here is a solution using SUMPRODUCT.

2-tab clothing bank example.xlsx
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

User avatar
hamster
StarLounger
Posts: 58
Joined: 10 Mar 2021, 22:57

Re: weekly summary sheet Excel

Post by hamster »

luv2bounce wrote:
10 May 2023, 09:18
I think I can make the pivot table work - how are you getting the full year?
Power Query Editor:
there is a list of dates from 01/01/2023 to 31/12/2023

Code: Select all

= {Number.From(#date(2023,1,1))..Number.From(#date(2023,12,31))}

snb
4StarLounger
Posts: 547
Joined: 14 Nov 2012, 16:06

Re: weekly summary sheet Excel

Post by snb »

You'd better use columns G and H in 'Issued' to store 'date' and 'quantity'.
The better the data sctructure the simpler any analysis.

User avatar
luv2bounce
StarLounger
Posts: 59
Joined: 07 Dec 2010, 12:21

Re: weekly summary sheet Excel

Post by luv2bounce »

:thankyou: Wonderful, wonderful, wonderful!!!!! I am starting to understand more! The sumprodiuct was the original type of formula I was looking for, but couldn't figure out how to make it work. Thank you so much @Hans for showing me! And Thank you also @hamster adn @smb for your suggestions about structuring and use. I am very, very grateful for your help and patience with me!

I don't have experience with Power Query yet, so will start looking into that to see what I can learn.

Honestly, I am so grateful to you all!!!!! :thankyou: :thankyou: :thankyou: :thankyou:

User avatar
hamster
StarLounger
Posts: 58
Joined: 10 Mar 2021, 22:57

Re: weekly summary sheet Excel

Post by hamster »

luv2bounce wrote:
10 May 2023, 15:10
I don't have experience with Power Query yet, so will start looking into that to see what I can learn.
see link in my sig

User avatar
luv2bounce
StarLounger
Posts: 59
Joined: 07 Dec 2010, 12:21

Re: weekly summary sheet Excel

Post by luv2bounce »

hamster wrote:
10 May 2023, 19:25
luv2bounce wrote:
10 May 2023, 15:10
I don't have experience with Power Query yet, so will start looking into that to see what I can learn.
see link in my sig
:thankyou: :thumbup: most definitely will! Very grateful for the help!