countifs help
I am building a countifs in smartsheet summary but its not counting. Can anyone help me.
=COUNTIFS(Location:Location, "Grise Ford", Location:Location, "Resolute Bay", Location:Location, "Pond Inlet", Location:Location, "Arctic Bay", Location:Location, "Igloolik", Location:Location, "Clyde River", Location:Location, "Sanirajak", Location:Location, "Qikiqtarjuaq", Location:Location, "Pangnirtung", Location:Location, "Kinngait", Location:Location, "Kimmirut", Location:Location, "Sanikiluaq")
Thank you so much
Best Answer
-
@tracyashton The other thing I thought about is if you need to return a total that consists of all of the cities you listed you could use COUNTIFS with an OR function, as pictured below. That would return the total number that contained every single one of those individual locations:
But again, there could only be one city listed under each location cell.
Answers
-
Are you trying to count if [Location] is any one of those? Is it a multi-select dropdown? What are you expecting to happen with your formula?
-
@tracyashton the COUNTIFS function is designed to count all of the times each condition is met that is set for the COUNTIFS. So your formula is essentially stating, "Count the number of times the location is Girse Ford AND the location is Resolute Bay AND the location is Pond Inlet AND th….etc. One single location cell would literally have to be meeting all of those conditions.
So if you are trying to count the number of times that Grise Ford is in your list the equation would simply be:
=COUNTIFS(Location:Location,"Grise Ford")
Below is a sample I made for you. I listed the location names you used (most of them) and repeated a few of them randomly to give variable data. Then on the right I made a tally area so you could see the formula working.Please let me know if you need additional help!
-
@tracyashton It wouldn't let me tag you in my reply above. But please let me know if you need anything else.
-
@tracyashton The other thing I thought about is if you need to return a total that consists of all of the cities you listed you could use COUNTIFS with an OR function, as pictured below. That would return the total number that contained every single one of those individual locations:
But again, there could only be one city listed under each location cell.
-
First off thank you all for your responses. You are right @Brian Wilson DC I need to put the OR in so it can count the sites from the drop down without thinking its and.
Thank you for your help. I had been struggling to figure out why it was always zero.
Have a great day
Tracy
-
I hade built the countifs for each location and had a sum for those counts. Thanks again for your help.
-
@tracyashton I'm glad that it all worked out! If you ever need anything feel free to ask! Have a wonderful week!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!