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
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
-
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
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
-
Your amazing Thank you! Can't believe I Missed that!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!