COUNTIF syntax - count if specific word in cell

dianay
dianay ✭✭✭
edited 12/09/19 in Smartsheet Basics

I'm trying to count every cell in a column that has a specific word (the word "Store") in it, but I keep getting an "Incorrect Argument Set" error.

This is my formula syntax - what needs to be corrected?

=COUNTIF([Location Type]:[Location Type] = "Store")

The name of the column is "Location Type" and I would like for the function to search every row in this column. Thank you.

Comments

  • Mike L.
    Mike L. ✭✭✭
    edited 03/14/19

    I think you have a syntax issue. You need a comma between the range and the criterion.  


    Sample Usage





    COUNTIF(Quantity:Quantity, >25)







     


    Syntax


    COUNTIF(range, criterion)

    https://help.smartsheet.com/function/countif

    Also if your just looking to match text I think you can also drop the operator.  But play around with it a bit to see what work.  The reason I say this is that if you look at the sample usage for "countifs", you'll see they are searching a range for matches to text = "T-Shirt" but no operator is needed.  

    Sample Usage





    COUNTIFS(Quantity:Quantity, >25, [Item Name]:[Item Name], "T-Shirt")


     


    I find the syntax for countif/countifs, sumif/sumifs is quite tricky. I often need to break it down to simpler expressions just to make sure I have the formula syntax correct then I substitute back in the criteria  I want to evaluate. 


     


    This is something I was working on this week. It took me several tries to get it right. When I want to use countifs or sumifs I start it with a single (range, criterion) to get it working then add additional range/criterion expressions. 



     =SUMIFS(Amount:Amount, Obj:Obj, AND(@cell <> "5282", @cell <> "5334", @cell <> "5333"))

    Also if your expression doesn't seem to be working check the data type.  

    Hope this helps. 

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Mike,

     

    You are correct that if you are looking for a specific value (whether number, symbol, text, or combination thereof) you don't need an operator.

     

    =COUNTIF([Location Type]:[Location Type], "Store")

     

    will work.

  • dianay
    dianay ✭✭✭

    Thank you both! That resolved the issue.