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

Options
edited 08/03/20

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!

• ✭✭✭✭✭✭
Options

Try this:

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

• ✭✭✭✭✭✭
Options

Try this:

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

• Options

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

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!