Formula for COUNTIF
Answers
-
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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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.
-
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)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
=COUNTIFS({Sick Call Spreadsheet Range 2}, DATEONLY(@cell) = Dates@row)
-
What column type is the formula in? What type of column is {Sick Call Spreadsheet Range 2}?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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.
-
Are there any errors in Range 2? How exactly is that populated?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
range 2 is sick or returned
range 4 are the dates
-
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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@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.
-
Try working with something like this...
=COUNTIFS({Other Sheet Status Column}, "Sick", {Other Sheet Date Column}, @cell >= TODAY(-7))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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?
-
=COUNTIFS({Other Sheet Status Column}, "Sick", {Other Sheet Date Column}, @cell = Date@row)
A basic cell reference should do the trick.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thats IT !!!!! thank you thank you thank you !!!!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 302 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!