How do I extract data from a column that represents a week or month?
Lets say I count apples sold and I input the data in column [Number of apples sold] which is next to a date column. Now I want to find out how many apples I sold in a given week. I use this formula:
=SUMIFS([[Number of apples sold]:[Number of apples sold], Date:Date, WEEKNUMBER(@cell) = 44, Date:Date, YEAR(@cell) = 2021)
This works but I have to change the WEEKNUMBER for every week which is a lot of work and I will also have to update the YEAR figure every year.
I use the same method for extracting "apples sold" on a monthly basis with the same problem.
I think the solution is to compare the WEEKNUMBER of the Date column with a helper column that has the WEEKNUMBER repopulated. I just can't get that to work.
Thanks for the help!
Help Article Resources
Check out the Formula Handbook template!