Forum Discussion
I need help with creating a formula
To display date in calendar week you can use the formula: =WEEKNUM(A4) in D4.
This article describes the formula syntax and usage of the WEEKNUM function in Microsoft Excel.
After that, you could add up the calendar week with a simple sum.
Here is a small example of the formula: =SUMIF(D4:D35,D4,E:AA)
Hope I was able to help you with this info.
I know I don't know anything (Socrates)
NikolinoDE thank you, but I think I did not explain properly what I actually need help in. The first screen shot picture that I shared is exactly what you had explained. What I am having a challenge with is how to use xIfn and UNIQUE properly, after getting the number of weeks, how do I filter the data further?
- From the screen captures it looks like you're using Excel 2016 which doesn't support UNIQUE and dynamic arrays. Analyzing this data set might be a small chore with formulas in that version. Another approach to make is tabling the data and unpivoting from Column E to the end which will make your data run mostly vertical. From there you could pivot the data. If you're able to upload a mock sample of the data set, myself or someone else could arrange it for you.
Patrick2788 thank you so much, I am trying the pivot suggestion right now. My computer has Excel 2019.
This is the section, I am struggling with
