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!
Wolfram
Best Answers
-
Hi @Wolfram
You'll need to have the week or month listed somewhere, either directly in the formula or in a cell you can reference.
For example, if you have a column with all the week numbers listed down each row, then you can use the cell from that column as the week number to look for:
=SUMIFS([Number of apples sold]:[Number of apples sold], Date:Date, WEEKNUMBER(@cell) = [Week Number]@row, Date:Date, YEAR(@cell) = Year@row)
Is that what you were looking to do?
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Answers
-
Hi @Wolfram
You can use the TODAY function to help you with this!
Are you looking to calculate last week's totals? If so, we can find the WEEKNUMBER(TODAY()) - 1, which will tell us last week's number.
Then we can do the same thing with the YEAR function... YEAR(@cell) = YEAR(TODAY())
Try this:
=SUMIFS([Number of apples sold]:[Number of apples sold], Date:Date, WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()) - 1, Date:Date, YEAR(@cell) = YEAR(TODAY()))
For your month formula, I would presume it's the same sort of thing. If you want to find last month, you could do this:
=SUMIFS([Number of apples sold]:[Number of apples sold], Date:Date, MONTH(@cell) = MONTH(TODAY()) - 1, Date:Date, YEAR(@cell) = YEAR(TODAY()))
However this gets a bit complicated if you're in January. I would write two formulas and have an IF statement identify what month you're in, like so:
=IF(MONTH(TODAY()) = 1, SUMIFS([[Number of apples sold]:[Number of apples sold], Date:Date, MONTH(@cell) = 12, Date:Date, YEAR(@cell) = YEAR(TODAY()) - 1), SUMIFS([Number of apples sold]:[Number of apples sold], Date:Date, MONTH(@cell) = MONTH(TODAY()) - 1, Date:Date, YEAR(@cell) = YEAR(TODAY())))
Let me know if this is the right solution for you or not.
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Thanks!
I don't want to have the data for last week or last month. I want to have the data for a given week or month. E.g. week 22 or month 8.
What I want to avoid is having to input the week number 52 times.
-
Hi @Wolfram
You'll need to have the week or month listed somewhere, either directly in the formula or in a cell you can reference.
For example, if you have a column with all the week numbers listed down each row, then you can use the cell from that column as the week number to look for:
=SUMIFS([Number of apples sold]:[Number of apples sold], Date:Date, WEEKNUMBER(@cell) = [Week Number]@row, Date:Date, YEAR(@cell) = Year@row)
Is that what you were looking to do?
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!