# SUM and TODAY() function

Hello,

I am building a formula to give me the SUM of the # of spots for dates called within the last 14 days. I was able to build a formula using the TODAY function:

=SUMIF({# of Spots}, <TODAY(), AND({Date Calleds}, <TODAY()))

However this formula returns a #INVALID DATA TYPE error.

I added a number to the TODAY function, and I get the same error.

=SUMIF({# of Spots}, <TODAY(-14), AND({Date Calleds}, <TODAY(-14)))

I am not sure how to make this function work.

Thank you

FL

Tags:

• ✭✭✭✭✭✭

I've updated the formula, so the ranges are correct.

I think Debbie missed removing the double ranges as they were in there from the start when it was set up to reference only the same sheet.

Did it work?

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭

The error message suggests to me that you asking the criteria based on a date function, but the range of data to which the criteria is being applied, isn't a date column.

Would that sound right?

Can you give a screen shot of the # of spots column and describe what you are wanting the formula to do, I could then maybe suggest a formula that would work for you?

Kind regards

Debbie

• What I am trying to do is only display the total of vacancies for calls that have been made in the last 14 days.

Right now I have formulas showing the overall total for the entire spreadsheet. But I only want the totals to display for calls made within the 14 days.

• ✭✭✭✭✭✭

Thank you for showing the screen and explaining your aim.

Have a go at the function below, I believe it should do what you are asking for.

The way this reads is Total the # of Spots column if the Date Called is less than Today and greater than Today -14 days. (i.e. total the # of Spot if the date called is within the last 14 days.

=SUMIFS([# of Spots]:[# of Spots], [Date Called]:[Date Called], <TODAY(),[Date Called]:[Date Called], >TODAY(-14))

• Thank you for your help. I added the formula just as you indicated. I get the #UNPARSEABLE error. Not sure why an error stills populates.

=SUMIFS([{# of Spots}]:[{# of Spots}], [{Date Calleds}]:[{Date Calleds}], <TODAY(), [{Date Calleds}]:[{Date Calleds}], >TODAY(-14)).

• ✭✭✭✭✭✭

Are you referencing another sheet or only the same sheet?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• I am referencing another sheet.

--Fl

• ✭✭✭✭✭✭
edited 12/21/20

Hi, sorry I had assumed the same sheet, hope this helps.

If another sheet try the following, the name inside the {} is the name of the range that refers to the # of Spots column and the Date Called column. You will need to amend the names to match your range references:

=SUMIFS({# of Spots}:{# of Spots}, {Date Called}:{Date Called}, <TODAY(), {Date Called}:{Date Called}, >TODAY(-14))

• ✭✭✭✭✭✭

Ok.

Then you'll have to remove the [ ] square brackets in the formula.

Did that work?

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• I removed the brackets and used the formula @Debbie Sawyer suggested and still I get the error.

=SUMIFS({#ofSpots}:{#ofSpots}, {Date Calleds}:{Date Calleds}, <TODAY(), {Date Calleds}:{Date Calleds}, >TODAY(-14)

• ✭✭✭✭✭✭

Make sure that you're cross-sheet references are names correctly.

I'd be happy to take a quick look if the reference names are correct.

Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• I'd be happy to.

--FL

• ✭✭✭✭✭✭

I've updated the formula, so the ranges are correct.

I think Debbie missed removing the double ranges as they were in there from the start when it was set up to reference only the same sheet.

Did it work?

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• Thank you both!!! It works now. 😀

• ✭✭✭✭✭✭

Well done (it looked like there was a missing ) at the end of the latest formula you posted too.

All sorted - Well Done!

• ✭✭✭✭✭✭

Excellent!

You're more than welcome!

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!