Tracking Space with Nested AND/OR statement?

Eric Tosso

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?

Paul Newcome

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))

Paul Newcome

Can you provide a screenshot of your sheet? Sensitive/confidential data can be removed, blocked, and/or replaced as needed.

Eric Tosso

Here it is Paul. Thanks for the help.

Paul Newcome

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))

Eric Tosso

That did the trick. Thanks again Paul.

Paul Newcome

Happy to help. 👍️