Creating a report using COUNTIFS with multiple fields and date range

Hi:

I have a schedule Smartsheet that I am attempting to build a report via COUNTIFS formulas which pulls data from the schedule smart sheet.

My schedule smartsheert (source data) has the following fields & Column Values, I'm using to attempt this report:

Individual Names (Text), Status (Text), Start Date (Date), End Date (Date)

I have the following formula:

=COUNTIFS({2019 H1 NAMER Schedule - Instructor}, "NAME", {2019 H1 NAMER Schedule - Activity H1}, "FREE", {2019 H1 NAMER Schedule - Start Date}, >=DATE(2019, 1, 1), {2019 H1 NAMER Schedule - Start Date}, <=DATE(2019, 12, 31)+COUNTIFS({2019 H1 NAMER Schedule - Instructor}, "NAME", {2019 H1 NAMER Schedule - Activity H1}, "FREE", {2019 H1 NAMER Schedule - Start Date}, >=DATE(2019, 1, 1), {2019 H1 NAMER Schedule - Start Date}, <=DATE(2019, 12, 31)))

This is intended to count how many times a person's status = "Free" for each date range. The formula above works without the addition of the second COUNTIFS. So the following formula works correctly:

However, when I add the + and rest of formula I get an "incorrect operation" error. I have used + with multiple COUNTIFS before, but am stuck on this one. Seems, the date range is throwing error.

Thanks!

Find more posts tagged with

Formulas

Comments

Andrée Starå

Hi,

Can you share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Paul Newcome

You are putting a closing parenthesis in the wrong place. You should have 2 closing parenthesis before the +. One to close the DATE function and another to close the first COUNTIFS. You have three at the end of the overall formula when you should only have two (close the DATE and second COUNTIFS functions).

You have

=COUNTIFS(...........DATE(...) + COUNTIFS(............DATE(...)))

It should be

=COUNTIFS(...........DATE(...)) + COUNTIFS(............DATE(...))

Alejandra

Good catch Paul! I just wanted to chime in and offer some help articles for future reference:

Formula Errors: https://help.smartsheet.com/articles/2476176-formula-error-messages#invalidoperation
Formula Operators: https://help.smartsheet.com/articles/2476171-create-and-edit-formulas-in-smartsheet

Sign in to join the conversation!

Quick Links

Trending discussions

Contact List showing as a text only
It happened suddenly. It was working last week. I'm using Index Collect to pull a contact as cross sheet reference. But today, I did not see that working well. Usually it has circle beside my name, suddenly it vanishes, and it just contains text. I'm using those other columns to be sent notification, and it doesn't work…
Formula to Convert Scientific Notation to Numerical Value
Hi, I am trying to create a sheet from data that is originally held in .csv files. My goal is to make it so that transferring the data to my sheet should be as simple as just copy and paste. My issue is that two of the fields that I need to copy over are reported in scientific notation (1.3E+004) and I am trying to figure…
Calculating Quarters with a Start and a Finish date
I searched the community and did not see a formula that calculated Quarter based on a Start and a Finish date while also accounting for cases where the year for the Start and Finish date differ. The formula below handles that. A few notes … It assumes that Finish is always >= Start. The entire formula is wrapped with…