COUNTIFS/CONTAINS FUNCTION

24

Answers

  • I am having a similar but perhaps slightly different issue. I am wondering when using the Countifs function am i able to use 3 sheet references and count the unique number of times a month is occuring for a particular Event Type within a event topic.

    e.g. Formula which only returns if there the month exists not how many times.

    Summary Sheet Data


    Source Sheet Data

    Apologies if this is unclear, I am trying to summarize how many times an Event Topic happens per month wit a particular communication medium.

    thans,

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Bradley Lindsay Your current formula should be working. Are you able to provide a screenshot that shows the source data where the COUNTIFS is producing the wrong number?

    thinkspi.com

  • I'm not able to get my formula to work either. What am I doing wrong? They both say 0



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Desirae Borge Replace your second range reference with @cell.

    thinkspi.com

  • I have a follow up question to the initial countifs and contains question/solution.

    First after finding this string, I found the solution to exactly what I was looking for on a single possible criteria.

    My question is how do I, if possible write a function that will search for 2 possibilities

    =COUNTIFS(Sunday2:Sunday14, CONTAINS("0600-1400", @cell))

    I am working on a sheet that will auto count total staff scheduled on any given day when the schedule is filled in. Each column is a day of the week. With the string noted above, it works great however does not count total staff as we have two schedules for the shift I am working on, 0530-1330 and 0600-1400. When I use the string above for the 0600-1400 schedule it works as expected; however how would I introduce the second schedule to the same string to make the count for any cells that contain 0530-1330 or 0600-1400.

    The individual cells unfortunately do not only contain the scheduled hours but also a location assignment to various locations thus the "contains" need.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Davidp Try this...

    =COUNTIFS(Sunday2:Sunday14, OR(CONTAINS("0600-1400", @cell), CONTAINS("0530-1330", @cell)))

    thinkspi.com

  • Amber Jackson
    Amber Jackson ✭✭✭✭✭✭

    @Paul Newcome If you are ever in South Carolina, I owe you a drink--you saved my sanity with @row, @cell!!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Amber Jackson Haha. Deal. I am in West Virginia though, so that's a little bit of a hike for me. At least it is still east coast. Hahaha

    thinkspi.com

  • Katie Williams
    Katie Williams ✭✭✭✭✭✭

    @Paul Newcome WV in the house!! 💛 #almostheaven

  • MaryAnn Amari
    MaryAnn Amari ✭✭✭✭✭✭

    Hi everyone - I'm trying to countif entries of part numbers contain a "c" in them. For example 12c12345 or 12C12345 or 12354c vs not counting any part number entry that doesn't include a C. Any way to do that? This isn't cutting it: =COUNTIF(MyColumnName:MyColumnName, CONTAINS("c", @cell)).

    Thanks in advance.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    If the string does not contain a letter in it, then it is probably being stored as a number as opposed to a text string. This means that your data is not all formatted the same (numbers in some cells and text in the other). Insert a new column and enter

    [email protected] + ""


    And set that as a column formula. This will convert everythign into a text string even those that do not contain a letter. You can then use this new column in your COUNTIFS, and it should be working for you.

    thinkspi.com

  • Hello @Paul Newcome . I'm hoping you can help me with an issue i'm having using COUNTIFS with CONTAINS.

    I'm trying to count all the instances where a check box is checked off in a checkbox type column and the job title contains "manager". Here is the formula I'm using that I think should be working based on everything I've read, but it is returning #INCORRECT ARGUMENT and I'm not sure why.

    The "Seller 1:1 (NAM)" is the checkbox column title, and the "Job Title" is the column title for the other range.

    =COUNTIFS([Seller 1:1 (NAM)]:[Seller 1:1 (NAM)], =1, [Job Title]22:[Job Title]257, CONTAINS("manager", @cell))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Clark Coles It is because your first range is the entire column but the second range is rows 22 - 257. Try correcting this so that both ranges are the same size and see if that clears up your problem.

    thinkspi.com

  • =COUNTIFS({{Customer Range Complete}, Find("Active [In-Progress]"), {{{Customer Range Complete}}, FIND("Greenfield")} I am getting an unparseable error on this formula trying from same worksheet count the number of one range only if on other columns it has Active and Greenfield

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Jenn Pitt If it is in the same sheet, you will need to use

    [Column Name]:[Column name]

    as your ranges.

    The columns that contains "Active" and "Greenfield"... Do those cells have additional text within them or is it only the word "Active"/"Greenfield" in the cells?

    thinkspi.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!