SUMIFS with criterion referencing 2 date cells
Dear Forum,
i would like a SUMIFS formula to be able to count hours in my timesheet on certain dates. All my hours and dates sit in one sheet, id like a second sheet to then count hours in certain dates.
As i understand it, SUMIFS needs:'
'Andi Hawes Timesheet Range 2' as the range i want to sum up in a nother sheet
Andi Hawes Timesheet Range 1 is the range of datesin a nother sheet,
'[Column7]11' is my from date, in a formula as its going to be flexible in my current sheet,
'[Column8]11' is my 'to' date in my current sheet,
but when i build myself the formula, i get as far as:
=SUMIFS({Andi Hawes Timesheet Range 2}, {Andi Hawes Timesheet Range 1}, <=[Column7]11)
and the formula builder doesnt allow me to add a second criterion?!!? This formula works as it counts all my hours from whatever date i add into '[Column7]11', but id like to add an upper limit of 'to' with the second criterion, but im unable to????
Any ideas?
Answers
-
Hi Andi,
When adding in a second criteria, even if it's referencing the same range as your first one, you need to add in the range again. (Click here for the Help Center article on SUMIFS)
For example, try:
=SUMIFS({Andi Hawes Timesheet Range 2}, {Andi Hawes Timesheet Range 1}, <=[Column7]11, {Andi Hawes Timesheet Range 1}, >=[Column8]11)
Keep in mind that <= means before a date, and >= means after.
Let me know if this works! If not, it would be helpful to see some screen captures of your sheets (but please block out any sensitive data).
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.4K Get Help
- 424 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!