SUM(IFERROR(INDEX(COLLECT(MATCH))))

I may have just the brackets wrong or overthinking this but I need to return the SUM of column 'eOEM' when the 'Date of report' column is in the next 7 days AND the 'transaction status' column is "New". The formula I have is =SUM(IFERROR(INDEX(eOEM:eOEM, COLLECT(MATCH("New", [Transaction Status]:[Transaction Status], TODAY(7), [DATE OF REPORT]:[DATE OF REPORT], )), 0,""))) … can anyone help please?

Also trying =SUMIFS(eOEM:eOEM, [Transaction Status]:[Transaction Status], "New", [DATE OF REPORT]:[DATE OF REPORT], TODAY(7)) which doesn't work

I also cannot upload the small jpg screenshot to show this by copy and pasting, attach image or add attachment options.. just keeps crashing MS Edge!

Find more posts tagged with

Accepted answers

Paul Newcome

Try this:

=SUMIFS(eOEM:eOEM, [Transaction Status]:[Transaction Status], "New", [DATE OF REPORT]:[DATE OF REPORT], AND(@cell >= TODAY(), @cell <= TODAY(7)))

All comments

Paul Newcome

Try this:

=SUMIFS(eOEM:eOEM, [Transaction Status]:[Transaction Status], "New", [DATE OF REPORT]:[DATE OF REPORT], AND(@cell >= TODAY(), @cell <= TODAY(7)))

Lara Homewood

YOU ARE A SUPERSTAR!!! THANK YOU SO MUCH :)

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.