Formular for calculating total amount for each department by month, this week, last week and a day
Hi Guys,
I'd like to create a metrics that calculates an amount for each department by this year, last month, this month, this week and last week. Btw i will be refrerencing from another sheet "Sheet - Transport Request". Can you guys help with a formular? Technically, I need help with 4 formulars.
I'm sorry if I am not making it clear. Ill try my best to explain more.
Best Answer
-
I presume you have a Date column on your first, reference sheet as well. Is that correct?
If so, try the following:
Total this Year Formula
=SUMIFS({Calculate Duration Column}, {Department Column}, [Primary Column]@row, {Date Column}, IFERROR(YEAR(@cell), 0) = 2023)
This assumes you want 2023 as the year, but you can swap that out to be 2022.
Total last Month
=SUMIFS({Calculate Duration Column}, {Department Column}, [Primary Column]@row, {Date Column}, IFERROR(MONTH(@cell), 0) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) -1))
This one is a little more complicated as I added in an IF statement at the end. It checks if today's month is January (1) and if so, it returns 12 as the month you're looking to SUM. Otherwise, it subtracts 1 from the current month.
Note that this does not take into account a YEAR, so if your sheet houses many years you'll also want to add in that criteria as well:
=SUMIFS({Calculate Duration Column}, {Department Column}, [Primary Column]@row, {Date Column}, IFERROR(YEAR(@cell), 0) = IF(MONTH(TODAY()) = 1, 2022, 2023), {Date Column}, IFERROR(MONTH(@cell), 0) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) -1))
Again, I used an IF statement to say that if the MONTH is January then we also want to look for the previous YEAR as well.
Total this Month
=SUMIFS({Calculate Duration Column}, {Department Column}, [Primary Column]@row, {Date Column}, IFERROR(YEAR(@cell), 0) = 2023, {Date Column}, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()))
This one is nice and straight forward. I've added in the year as well in case you have other dates in your sheet.
Total this Week
=SUMIFS({Calculate Duration Column}, {Department Column}, [Primary Column]@row, {Date Column}, IFERROR(YEAR(@cell), 0) = 2023, {Date Column}, IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()))
Instead of using MONTH or YEAR, now we can use WEEKNUMBER. Again, I've kept in your YEAR criteria, assuming you have multiple years in your source sheet.
Total last Week
=SUMIFS({Calculate Duration Column}, {Department Column}, [Primary Column]@row, {Date Column}, IFERROR(YEAR(@cell), 0) = IF(WEEKNUMBER(TODAY()) = 1, 2022, 2023), {Date Column}, IFERROR(WEEKNUMBER(@cell), 0) = IF(WEEKNUMBER(TODAY()) = 1, 52, WEEKNUMBER(TODAY()) -1))
For this last one, again we need an IF statement in there to check if today's week is the first one of the year. If it is, we want to return week number 52 from the previous year. Otherwise, simply subtract 1 from the current week number.
Here's more information for you on all of the different functions I used:
- TODAY Function
- Automatically update the TODAY function in formulas
- SUMIFS Function
- YEAR Function / MONTH Function / WEEKNUMBER Function
- IFERROR Function
- Create Efficient Formulas with @cell and @row
- Create cross sheet references to work with data in another sheet
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
I presume you have a Date column on your first, reference sheet as well. Is that correct?
If so, try the following:
Total this Year Formula
=SUMIFS({Calculate Duration Column}, {Department Column}, [Primary Column]@row, {Date Column}, IFERROR(YEAR(@cell), 0) = 2023)
This assumes you want 2023 as the year, but you can swap that out to be 2022.
Total last Month
=SUMIFS({Calculate Duration Column}, {Department Column}, [Primary Column]@row, {Date Column}, IFERROR(MONTH(@cell), 0) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) -1))
This one is a little more complicated as I added in an IF statement at the end. It checks if today's month is January (1) and if so, it returns 12 as the month you're looking to SUM. Otherwise, it subtracts 1 from the current month.
Note that this does not take into account a YEAR, so if your sheet houses many years you'll also want to add in that criteria as well:
=SUMIFS({Calculate Duration Column}, {Department Column}, [Primary Column]@row, {Date Column}, IFERROR(YEAR(@cell), 0) = IF(MONTH(TODAY()) = 1, 2022, 2023), {Date Column}, IFERROR(MONTH(@cell), 0) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) -1))
Again, I used an IF statement to say that if the MONTH is January then we also want to look for the previous YEAR as well.
Total this Month
=SUMIFS({Calculate Duration Column}, {Department Column}, [Primary Column]@row, {Date Column}, IFERROR(YEAR(@cell), 0) = 2023, {Date Column}, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()))
This one is nice and straight forward. I've added in the year as well in case you have other dates in your sheet.
Total this Week
=SUMIFS({Calculate Duration Column}, {Department Column}, [Primary Column]@row, {Date Column}, IFERROR(YEAR(@cell), 0) = 2023, {Date Column}, IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()))
Instead of using MONTH or YEAR, now we can use WEEKNUMBER. Again, I've kept in your YEAR criteria, assuming you have multiple years in your source sheet.
Total last Week
=SUMIFS({Calculate Duration Column}, {Department Column}, [Primary Column]@row, {Date Column}, IFERROR(YEAR(@cell), 0) = IF(WEEKNUMBER(TODAY()) = 1, 2022, 2023), {Date Column}, IFERROR(WEEKNUMBER(@cell), 0) = IF(WEEKNUMBER(TODAY()) = 1, 52, WEEKNUMBER(TODAY()) -1))
For this last one, again we need an IF statement in there to check if today's week is the first one of the year. If it is, we want to return week number 52 from the previous year. Otherwise, simply subtract 1 from the current week number.
Here's more information for you on all of the different functions I used:
- TODAY Function
- Automatically update the TODAY function in formulas
- SUMIFS Function
- YEAR Function / MONTH Function / WEEKNUMBER Function
- IFERROR Function
- Create Efficient Formulas with @cell and @row
- Create cross sheet references to work with data in another sheet
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Hi @Genevieve P. ,
I can't thankyou enough the formulars worked perfectly. Appreciate the help.
Thankyou!😊
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 137 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 485 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
