Tracking Space with Nested AND/OR statement?
I am trying to track the amount of space (square footage) that multiple projects use within my greenhouse and have been able to do this down to the month. However, I running into issues on tracking space down the individual days within the month. My current formula for space by month (using June as an example) is:
=SUMIFS($[Project Volume]:$[Project Volume], $[Start Month]:$[Start Month], <=6, $[End Month]:$[End Month],
I am not sure how to track the changes within the specific month though. I suspect there are some nested OR and/or OR statements needed but can't get them to work. The example below (shows updateed volume info for June 1) gives the response "#Inlavid Data Type."
=SUMIFS($[Project Volume]:$[Project Volume], $[Start Month]:$[Start Month], <=6, $[End Month]:$[End Month], OR(AND($[Month End]:$[Month End],6, $[Day End]:$[Day End],1)))
Any thoughts on how this can work?
Best Answer
-
You actually don't need to break each portion of the dates out into separate columns. You should be able to use something like this for a specific month (the example being June of 2020)...
=SUMIFS($[Project Volume]:$[Project Volume], [Desired Start Date]:[Desired Start Date], AND(IFERROR(MONTH(@cell), 13) <= 6, IFERROR(YEAR(@cell), 9999) >= 2020), [Anticipated End Date]:[Anticipated End Date], AND(IFERROR(MONTH(@cell), 0) >= 6, IFERROR(YEAR(@cell), 0) >= 2020))
To get a specific day it would look along the lines of (June 1, 2020 for example)...
=SUMIFS($[Project Volume]:$[Project Volume], [Desired Start Date]:[Desired Start Date], @cell <= DATE(2020, 06, 01), [Anticipated End Date]:[Anticipated End Date], @cell >= DATE(2020, 06, 01))
Answers
-
Can you provide a screenshot of your sheet? Sensitive/confidential data can be removed, blocked, and/or replaced as needed.
-
Here it is Paul. Thanks for the help.
-
You actually don't need to break each portion of the dates out into separate columns. You should be able to use something like this for a specific month (the example being June of 2020)...
=SUMIFS($[Project Volume]:$[Project Volume], [Desired Start Date]:[Desired Start Date], AND(IFERROR(MONTH(@cell), 13) <= 6, IFERROR(YEAR(@cell), 9999) >= 2020), [Anticipated End Date]:[Anticipated End Date], AND(IFERROR(MONTH(@cell), 0) >= 6, IFERROR(YEAR(@cell), 0) >= 2020))
To get a specific day it would look along the lines of (June 1, 2020 for example)...
=SUMIFS($[Project Volume]:$[Project Volume], [Desired Start Date]:[Desired Start Date], @cell <= DATE(2020, 06, 01), [Anticipated End Date]:[Anticipated End Date], @cell >= DATE(2020, 06, 01))
-
That did the trick. Thanks again Paul.
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!