Count dates and another column
Hi,
I am trying to check a dated to see if its week 1, week 2 etc and then count the total from another column.
A formula that would look up column "date" to see if the date range is within project calendar week (1,2,3 etc) and then total the number of hours within that date range.
hope someone can help ;)
Answers
-
Hi @Paul Molloy
How are you identifying that "week number"? Is this something stored in your other sheet, or do you mean the week of the year? If you mean the week of the year, then we can use the WEEKNUMBER Function to search for a specific week's number and SUM together the values in the other column based on this number.
For example, if you wanted to find the sum for this week (week 4), then you could do something like this:
=SUMIF({Date Column}, WEEKNUMBER(@cell) = 4, {Column to Sum})
or
=SUMIF({Date Column}, WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()), {Column to Sum})
See: SUMIF Function and Formulas: Reference Data from Other Sheets
Let me know if that makes sense!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Hey Guys Im trying to get a count of 2 columns that have specific criteria. For example how many times in the month of march we had tasks which are training tasks.
I created this formula but i'm only getting 0 as an output can anyone help?
=COUNTIFS({Project Category}, "Course Enhancement", {Start Dates}, AND(IFERROR(MONTH(@cell), 0) = 4, IFERROR(YEAR(@cell), 0) - 2022))
-
Hi @Earl P
At the end of your formula you have:
IFERROR(YEAR(@cell), 0) - 2022))
This is subtracting 2022 from your year, so if you have a date of April 2022, it's searching for April, 0000
Try using an = sign instead:
=COUNTIFS({Project Category}, "Course Enhancement", {Start Dates}, AND(IFERROR(MONTH(@cell), 0) = 4, IFERROR(YEAR(@cell), 0) = 2022))
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Your amazing Thank you! Can't believe I Missed that!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.4K Get Help
- 364 Global Discussions
- 200 Industry Talk
- 429 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 446 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!