SUM and TODAY() function

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!

All comments

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

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

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!

Hi @Andrée Starå

I am referencing another sheet.

--Fl

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?

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)

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?

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!