We have a form for tracking sick time. Employee must fill out the form on first day of sick. They have to fill out the same form again when they return to work. These are both tracked in the Status column (Sick and Returning). We also have another column titled quarantined and the drop down options are yes and no. This info fills to one sheet. I would like to COUNT all "Yes" responses to quarantine that have not yet answered returning. There will be multiple names on the sheet and most will have two entries as sick and returning. The only ones I would like to count are the people that have a sick entry and no returning entry yet AND have said yes to the quarantine question. Is this possible? Thank you

=COUNTIFS(Status:Status, "Sick", [Mandatory Quarantine]:[Mandatory Quarantine], "Yes") - COUNTIFS(Status:Status, "Returning", [Mandatory Quarantine]:[Mandatory Quarantine], "Yes") The above has the column names updated.

=COUNTIFS({Other Sheet Status Column}, "Sick", {Other Sheet Date Column}, @cell = Date@row) A basic cell reference should do the trick.

Formula for COUNTIF

Answers

Paul Newcome Community Champion

03/30/20

Great! Glad we were able to get it working for you. 👍️

CERTIFIED SMARTSHEET PLATINUM PARTNER
10x Time Savers (An exclusive group that grants you access our Smartsheet master classes, tips, tricks, & much more!)
Training opportunities with my team and I
0 · Share on Facebook Share on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Trending in Formulas and Functions

Character Limit Formula
I'm unable to convert this to column formula (probably due to the reference to my 'blank' column… However, this formula doesn't work, =IF(LEN([Source Admission ID]) >= 21, "Source Admission ID Limited to 20 VARCHAR", "") So, I changed it to this, which works… =IF(LEN([Source Admission ID]) >= 21, "Source Admission ID…
Workday is not omitting the holidays?
I have tested this manually, as well as outside of my formulas. I want to reference a date, and then search on the Holidays range (a list of holidays) and return the previous workday if the date is found in the holiday list. The formula ALWAYS returns the day before the date, whether it's in the holiday list or not. Any…
I want to send automated email to "assigned to", from a sheet, but only one reminder email vs mutlip
I have title, "assigned to" column, status(task 1), due date (task 1) columns, status(task 2), due date(task 2)…it goes for at least 10 tasks. The conditions to trigger the emails are, trigger 2 days before due date(when status is not equal to completed) and leave weekends trigger on due date(when status is not equal to…

Formula for COUNTIF

Answers

Help Article Resources

Categories