SUMIFS in date range using CHILDREN only
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?
Best Answer
-
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))
Answers
-
Which column are you putting this formula in the parent rows of?
-
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?
-
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))
-
Awesome, thank you Paul. I will remember about the @row next time!
-
Help Article Resources
Categories
Check out the Formula Handbook template!