Formula for COUNTIF

Ryan Murphy

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

Paul Newcome

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

Paul Newcome

=COUNTIFS({Other Sheet Status Column}, "Sick", {Other Sheet Date Column}, @cell = Date@row)

A basic cell reference should do the trick.

Paul Newcome

Try something like this...

=COUNTIFS(Status:Status, "Sick", Quarantine:Quarantine, "Yes") - COUNTIFS(Status:Status, "Returning", Quarantine:Quarantine, "Yes")

Ryan Murphy

Comes back as unparseable...

The quarantine dropdown options are Yes, No and N/A. Not sure if that has any affect on it.

Paul Newcome

What are your exact column names?

Ryan Murphy

Status and Mandatory Quarantine.... I added the mandatory already to the formula... still comes back with error

Paul Newcome

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

Ryan Murphy

Thank you!!!!!

Paul Newcome

Happy to help! 👍️

Ryan Murphy

Paul one more for you... I need the same as above and I want to break it down further. We have a schedule column and the responses are either A or B. How do I get a breakdown of these?

Ryan Murphy

Disregard... figured it out

Paul Newcome

Glad you figured it out!

Ryan Murphy

Paul, one more updated question... I'm referencing another sheet for this and it's coming back unparseable (same sheet being referenced for both)

=COUNTIFS({Sick Call Spreadsheet Range 1}, Status:Status, "Sick", {Sick Call Spreadsheet Range 3}, Unit:Unit, "SOU")

What is wrong here?

Paul Newcome

You don't have to type out the column names when referencing another sheet. When you create the cross sheet reference, you select the range and just leave it at that.

=COUNTIFS({Sick Call Spreadsheet Range 1}, "Sick", {Sick Call Spreadsheet Range 3}, "SOU")

Ryan Murphy

Thanks again @Paul Newcome

Final question...

3/26 4

3/27

3/28

3/29

3/30

3/31

Is there a way to reference another sheet that contains the data to enter the number of "sick" on today's date. So for the future dates (27, 28, 29, 30, 31) would populate when they become the current date. I'm guessing there is a TODAY function/formula, but I can't find it.

Thanks again.

Paul Newcome

I believe there is a way (there is in fact a TODAY() function), but I am not sure I follow exactly what you are wanting to do...

So you want to have a date column with the dates going down the rows. You then want to have a count for how many have a "Sick" entry but not a "Returning" entry for each particular date?

Ryan Murphy

no longer concerned with returning... The daily sick count will vary from day to day. I would like to have a count per day. looking for a formula that would place the amount of entries per date.