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

Lara Homewood

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!

Paul Newcome

Try this:

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

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