SUMIFS in date range using CHILDREN only

Sam Catchpole

Hi,

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?

Paul Newcome

Nope. Just wanted to make sure we got the syntax right is all.

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

Paul Newcome

Which column are you putting this formula in the parent rows of?

Sam Catchpole

Hi Paul, it is in a new column labelled by project quarter. So this formula is in a column called Q2. Do I need to reference that somewhere?

Paul Newcome

Nope. Just wanted to make sure we got the syntax right is all.

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

Sam Catchpole

Awesome, thank you Paul. I will remember about the @row next time!

Paul Newcome

Happy to help. 👍️