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?

Find more posts tagged with

Accepted answers

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

All comments

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. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.