Help with CountIf
Hi Everyone,
I am new to formulas and have been struggling with a CountIf (I'm sure this won't be my last questions).
I've watched videos and looked at past logs but I still can't wrap my brain around this simple request.
I am trying to count the number of instances where a specific location has an open request that is notated by a check box. In this case, I want to count where the "open requests" are thus the checkbox would not be selected. I am pulling from a reference sheet into my roll-up sheet.
Here is the formula I have (that is incorrect)
=COUNTIFS({On the Job Injury Log Range 9}, "Baltimore City Correctional Ce", {On the Job Injury Log Range 11}, 0)
Any help in figuring out where I went wrong would be appreciated.
Thanks!
Tabitha
Best Answer
-
Yes. THat first one should do the trick.
As for the <= 60 bit...
You would use either a COUNTIFS to repeat the range and do your criteria of >= 1 for the first range and<= 60 for the second repetition of the range OR you could use a COUNTIF with the AND. It is your choice. I personally always use COUNTIFS even if I only have a single range.
=COUNTIFS({On the Job Injury Log Range 10}, >=1, {On the Job Injury Log Range 10}, <=60)
or
=COUNTIF({On the Job Injury Log Range 10}, AND(@cell >= 1, @cell <= 60))
or my personal preference
=COUNTIFS({On the Job Injury Log Range 10}, AND(@cell >= 1, @cell <= 60))
Answers
-
What are you getting? Are you getting an incorrect count or an error? What columns are your cross sheet references pointing at?
-
Hi Paul!
Trial, error and lots of community reading and I got it to work-
=COUNTIFS({On the Job Injury Log Range 9}, "Baltimore City Correctional Ce", {On the Job Injury Log Range 11}, 0)
Now I'm stuck on a formula for <=60
I get the error of incorrect argument
=COUNTIF({On the Job Injury Log Range 10}, >=1, (AND({On the Job Injury Log Range 10}, <=60)))
-
I think I figured this out too- I needed it to be =CountIfs NOT =CountIf
-
Yes. THat first one should do the trick.
As for the <= 60 bit...
You would use either a COUNTIFS to repeat the range and do your criteria of >= 1 for the first range and<= 60 for the second repetition of the range OR you could use a COUNTIF with the AND. It is your choice. I personally always use COUNTIFS even if I only have a single range.
=COUNTIFS({On the Job Injury Log Range 10}, >=1, {On the Job Injury Log Range 10}, <=60)
or
=COUNTIF({On the Job Injury Log Range 10}, AND(@cell >= 1, @cell <= 60))
or my personal preference
=COUNTIFS({On the Job Injury Log Range 10}, AND(@cell >= 1, @cell <= 60))
-
Thanks for that tip!
I REALLY appreciate it!
-
Happy to help! 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.2K Get Help
- 386 Global Discussions
- 212 Industry Talk
- 444 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 132 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 291 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!