Count Ifs Dates in the future with multiple conditions

I am trying to create a formula will return a count of dates 21 days from the current date, but also sorted by an Assigned persons name and multiple statuses of the request.

Here is a formula that I have so far:

=COUNTIFS([Assigned To]:[Assigned To], "Name", [Status]:[Status], "Scheduled", [Start Date]:[Start Date], <=TODAY(+21))

What I would like to do is to add 2 or 3 more Status criteria. Or would I have to recreate this formula for those individual criteria.

Find more posts tagged with

Formulas

Comments

Paul Newcome

You can add the additional criteria to your current formula by continuing the format.

criteria range 3, criteria 3, criteria range 4, criteria 4............

Phil B.

Ok, I asked the wrong question. I have multiple conditions in the Same Column. so

[Status]:[Status], "Scheduled" Can be; [Status]:[Status], "Tentative" or [Status]:[Status], "to be Scheduled" etc.

I believe I need to embed an Or statement in there but I am not sure. I would like to include all conditions in a final count.

Paul Newcome

If the status doesn't matter then you can just remove that criteria.

If you are looking to exclude blanks in the status column the range would be Status:Status and the criteria would be NOT(ISBLANK(@cell))

To include some but not all options, the criteria would be OR(@cell = "Scheduled, @cell = "Tentative", @cell = ...........)

If you want to include most of the options but exclude just a few, it would be shorter to use NOT(OR(@cell = ............)

Phil B.

Thanks for all the help so far.

This question is in reference to the NOT(ISBLANK(@CELL))

The below function works well by itself, but when I try to add another Range to querry i receive the #UNPARSABLE Error.

=COUNTIFS([Service Now #]:[Service Now #], NOT(ISBLANK(@cell)), [Responsible Person]:[Responsible Person], "Name")

Am I expecting too much of the function?

Paul Newcome

Not necessarily. The above formula SHOULD work assuming you are changing Name to whatever name you are looking for. What exactly are you trying to add?

Phil B.

I figure it out. For some reason your response and question back to me triggered something in my Smart Sheet addled brain and I figured out I had 1 too many parenthesis at the end of the formula. I've pasted a working function below:

The person's name does change for each person I need the formula to work for

=COUNTIFS([Service Now #]:[Service Now #], NOT(ISBLANK(@cell)), [Responsible QE]:[Responsible QE], "persons name", [Quality Status]:[Quality Status], OR(@cell = "", @cell = "Initial Review", @cell = "CAR/SCAR/NCR Issued", @cell = "On Hold"))

Thanks again!!!

Paul Newcome

If you have a "Master list" where each name is only listed once, and you are putting the formula next to that, you could change "persons name" to [Column Name Name is In]@row. That would be one less tweak you have to make for each individual formula. It would reference whatever name is next to it.

Sign in to join the conversation!

Quick Links

Trending discussions

Dashboard not updating
Hello Smartsheet Community! I created a formula that tracks the number of days away from the deadline that I posted on my dashboard as a visual for my students. =IF([Deadline Date]@row < TODAY(), "🔴 Past Due (" + ABS([Deadline Date]@row - TODAY()) + " days ago)", IF([Deadline Date]@row = TODAY(), "🟠 Due Today",…
Sheet Summary Formula Help
Hello, I am trying to get this formula to work but am not having any luck. When I remove the last @cell….text below the formula works great. When I add it in I get an error. Any ideas? =COUNTIFS([ACCT READINESS STATUS PLV]:[ACCT READINESS STATUS PLV], OR(@cell = "PENDING LOCAL CAH", @cell = "ADDITIONAL REVIEW NEEDED LOCAL…
How to use previous cell value (from cell history) in a formula?
I have seen this question asked multiple times in the forum, but I couldn't find any clear answer provided in the answers. Please excuse me if a solution already exists, but if not, will someone please let me know if it is possible to use a previous cell value (from cell history) in a formula? I am specifically trying to…