Count Ifs Dates in the future with multiple conditions

Phil B.

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.

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.