SUMIFS in date range using CHILDREN only



I'm trying to do what I thought was a fairly basic thing but I keep getting tripped up!

I have an expenditure spreadsheet with different categories divided into Parent -> Child -> Grandchild. I want to sum a column [Actual EX VAT] dependent on it happening within a date range in the [Order Date] column.

BUT to only include the items in its children.

I currently have the following in a cell that is a parent:

=SUMIFS(CHILDREN([Actual EX VAT]:[Actual EX VAT]), CHILDREN([Order Date]:[Order Date]), >=DATE(2021, 11, 1), CHILDREN([Order Date]:[Order Date]), <=DATE(2022, 1, 31))

Which works, sort of, but sums the whole of the [Actual EX VAT] column rather than just its children.

I have searched through previous questions without avail. Can anybody please help?

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!