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

  • Brian Wilson DC
    Brian Wilson DC ✭✭✭✭
    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

  • Paul Newcome
    Paul Newcome Community Champion

    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?

  • Brian Wilson DC
    Brian Wilson DC ✭✭✭✭

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

  • Brian Wilson DC
    Brian Wilson DC ✭✭✭✭

    @tracyashton It wouldn't let me tag you in my reply above. But please let me know if you need anything else.

  • Brian Wilson DC
    Brian Wilson DC ✭✭✭✭
    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.

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

  • Brian Wilson DC
    Brian Wilson DC ✭✭✭✭

    @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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!