COUNTIFS
Hi,
Not sure of the best method or formula, but I am trying to sum up (in column61) the total number of days worked in the last 2 weeks for everyone when the data looks like this:
I can do a countifs statement that returns the number of weekending dates that are within 2 weeks of the current rows weekending date, but not then be able to go on and add the number of days works on those rows.
=COUNTIFS([Weekending Date]:[Weekending Date], >([Weekending Date]@row - 14), [Weekending Date]:[Weekending Date], <([Weekending Date]@row + 1))
HELP!!!😁
Best Answer
-
Can I clarify, are you looking to SUM the data from the column [Week Total Decimal], but just for the two rows that are in the last two weeks relative to Today's date?
If so, we can use a SUMIF statement to find this data!
We will SUM the column [Week Total Decimal] if the date in the [Weekending Date] is within the last 14 days.
Try this:
=SUMIF([Weekending Date]:[Weekending Date], AND(@cell < TODAY(), @cell > TODAY(-14)), [Week Total Decimal]:[Week Total Decimal])
This looks for the date being less than TODAY but greater than 14 days ago.
Will this work for you? This would return only one number, so you wouldn't necessarily need an entire column for it. I would recommend using a Sheet Summary field, if you have access to this feature on your plan.
Cheers!
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
Can I clarify, are you looking to SUM the data from the column [Week Total Decimal], but just for the two rows that are in the last two weeks relative to Today's date?
If so, we can use a SUMIF statement to find this data!
We will SUM the column [Week Total Decimal] if the date in the [Weekending Date] is within the last 14 days.
Try this:
=SUMIF([Weekending Date]:[Weekending Date], AND(@cell < TODAY(), @cell > TODAY(-14)), [Week Total Decimal]:[Week Total Decimal])
This looks for the date being less than TODAY but greater than 14 days ago.
Will this work for you? This would return only one number, so you wouldn't necessarily need an entire column for it. I would recommend using a Sheet Summary field, if you have access to this feature on your plan.
Cheers!
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Hi @Genevieve P,
Thanks for you response. Yes SUMIFS were the way forward in the end that I tried last night. Had to add a couple of columns to sum the weekly hours and number of days in that specific row. Then used
=SUMIFS([No Days Worked]:[No Days Worked], ([Weekending Date]:[Weekending Date]), >([Weekending Date]@row - 14), [Assigned To]:[Assigned To], [Assigned To]@row, ([Weekending Date]:[Weekending Date]), <([Weekending Date]@row + 1))
So this then looks at the total number of days a person has worked across all contracts (as there is multiple lines for same week if the person has worked on multiple contracts) in a two week window.
Thanks again for the response!
Steve
-
No problem! I'm glad that you were able to figure it out. 🙂
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
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!