Formula for COUNTIF

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

Ryan Murphy

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 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)

Ryan Murphy

Ryan Murphy

=COUNTIFS({Sick Call Spreadsheet Range 2}, DATEONLY(@cell) = Dates@row)

Paul Newcome

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

Ryan Murphy

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 Newcome

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

Ryan Murphy

range 2 is sick or returned

range 4 are the dates

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

Ryan Murphy

@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 Newcome

Try working with something like this...

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

Ryan Murphy

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?

Paul Newcome

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

A basic cell reference should do the trick.

Ryan Murphy

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