Formula for COUNTIF

Formula for COUNTIF

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

Best Answers

Previous1

Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Try something like this...


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

  • Comes back as unparseable...


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

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    What are your exact column names?

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

  • Thank you!!!!!

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help! 👍️

  • 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?

  • Disregard... figured it out

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Glad you figured it out!

  • 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 NewcomePaul 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")

  • 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 NewcomePaul 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?

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

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Ok. So to do this we would need to find a way to match up your entries so that the Sick date and Returning date (or blank if not yet returned) are in the same row. Then we can do a COUNTIFS looking for overlap.


    Exactly what data is collected by the form? I think I may have an idea on how to make this work for you.

  • Several columns... sick or returned is the main drop down option, but we have the returned being removed with an automation. Created date is automatic. Name, assignment and assigned department is part of this as well.

    I'm looking to capture how many "Sick" were reported on a certain date. I would like to have a formula on a second sheet next to the next 14 days to auto fill the current day with how many come in throughout the day for a daily total once the new day cycles.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Oh. So you are just looking for how many submissions were made on a specific date? If only "Sick" are on the sheet, then you should be able to use something like this:

    =COUNTIFS({Other Sheet Submission Date Column}, DATEONLY(@cell) = [Date Column]@row)

  • =COUNTIFS({Sick Call Spreadsheet Range 2}, DATEONLY(@cell) = [email protected])

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    What column type is the formula in? What type of column is {Sick Call Spreadsheet Range 2}?

  • formula is in text. range 2 is a date.

    The first column is a date (starting today and going out a week), The next column is a text column where i want the formula. I want the formula on each of the next 7 days. When that respective day becomes the current day the formula will populate it.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Are there any errors in Range 2? How exactly is that populated?

  • range 2 is sick or returned

    range 4 are the dates

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Ok. So I think there may be a little miscommunication here...


    One post you said Range 2 is a date, and your last post you said Range 2 is the Sick/Returned.

    The range in your formula should be pointing at the dates.

  • @Paul Newcome , I’ll Rename the ranges. Basically what I want to do is look back each of the last seven days and see how many entries for sick there were and place them on a dashboard line graph.

    it was easier for me to add them to a different sheet to do the calculations. I just don’t know how to match them up with the proper date.

    The form column has “status” as either sick or returning. I would want to count the sicks. If they answer sick on the form they put first day missed and that column is called “first”.

    So in each of the last 7 days how many “sick”s were there per “first”. That would be a rolling number dependent on today’s days.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Try working with something like this...

    =COUNTIFS({Other Sheet Status Column}, "Sick", {Other Sheet Date Column}, @cell >= TODAY(-7))

  • that works great. Now on my secondary sheet I have a date column for the last week. I can manually run the above formula and take out the ">" to find each day. Is there anyway I can automate the last 7 days so it matches a date column in new sheet?

    Date Formula

    3/30/20

    3/29/20

    3/28/20

    3/27/20

    etc


    Right now i'm manually filling in the formula cells using your formula above. Is there any way to have your formula above to reference the date on the left and use that date to reference the "other sheet date column" to get the count?

  • Thats IT !!!!! thank you thank you thank you !!!!!

Sign In or Register to comment.