SUM and TODAY() function

Frances Lewis

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

Andrée Starå

@Frances Lewis

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?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Debbie Sawyer

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

Frances Lewis

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.

Debbie Sawyer

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

Frances Lewis

Hi @Debbie Sawyer

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

Andrée Starå

Hi @Frances Lewis

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

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Frances Lewis

Hi @Andrée Starå

I am referencing another sheet.

--Fl

Debbie Sawyer

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

Andrée Starå

@Frances Lewis

Ok.

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

Did that work?

Frances Lewis

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)

Andrée Starå

@Frances Lewis

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)

Frances Lewis

I'd be happy to.

--FL

Andrée Starå

@Frances Lewis

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?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Frances Lewis

Thank you both!!! It works now. 😀

Debbie Sawyer

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

All sorted - Well Done!

Andrée Starå

@Frances Lewis

Excellent!

You're more than welcome!

Glad we got it working!