Formula Help!

Options

Hi there - I have 2 questions regarding formulas:

  1. I am trying to count all the cells in a column that are not the value "Professional" in FY19. I seem to be getting it to work by using the formula =COUNTIFS(FY:FY, "FY19", Source:Source, "<>Professional"). However, this is not counting cells that are blank in the Source column. Is there a way to include blank cells?
  2. I am receiving errors when I try to embed an OR statement in a COUNTIFS function. I am looking to count the following:
  • FY = FY19
  • Entry Type = Commit or Incremental
  • Type = Intern or Programmatic Intern or Co-op

I would appreciate any help you can offer!

Laura

Best Answer

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 02/18/20
    Options
    1. Try moving the open quote.

    =COUNTIFS(FY:FY, "FY19", Source:Source, <> "Professional")


    2 --> Since you already know the proper syntax for a COUNTIFS, we just need to expand on that. OR functions require logical statements. When you are specifying the range separately from the criteria (such as in a COUNTIFS function), you use an

    @cell = "Commit"


    So knowing that we can build out an OR statement using the @cell reference.

    OR(@cell = "Commit", @cell = "Incremental")

    OR(@cell = "Intern", @cell = "Programmatic Intern", @cell = "Co-op")


    Then we just drop them into the criteria sections of the corresponding ranges.

    =COUNTIFS(FY:FY, "FY19", [Entry Type]:[Entry Type], OR(@cell = "Commit", @cell = "Incremental"), Type:Type, OR(@cell = "Intern", @cell = "Programmatic Intern", @cell = "Co-op"))

  • Lchicklis
    Lchicklis ✭✭✭✭
    Options

    Thank you! For #1, it still doesn't seem to be including blank cells, unfortunately. For #2, this worked! Thanks so much for your help :)

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Exactly what do you mean by "blank cells"? Is that particular column blank but there is data in another column, or are you referring to empty rows at the bottom of the sheet, or.....?

  • Lchicklis
    Lchicklis ✭✭✭✭
    Options

    Some cells in the "Source" column are blank, but I want them to be included in the count. There is data in other columns for those rows. I am looking at college reqs and sometimes the source was not filled out by the recruiter. In those cases, I still want to count them. I only want "Professional" pulled out separately.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Using <> "Professional" SHOULD count the blank cells in that case.


    Let's try this... Create a filter that mimics the formula and see how many rows are pulled.

  • Lchicklis
    Lchicklis ✭✭✭✭
    Options

    When I filter on the data, I get 98 rows meeting the conditions, but the formula is pulling 95. It looks like the difference is 3 blanks. I suppose I could just fill them in with something to make sure they get counted, but this is going to be a recurring data feed from another source, so I will continue to run into the issue of having blank cells in that particular column. Curious if there is a formula to specifically count blank cells?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    Give this a try...


    =COUNTIFS(FY:FY, "FY19", Source:Source, OR(@cell <> "Professional", ISBLANK(@cell)))

  • Shajahan Shabudeen
    edited 02/19/20
    Options

    Okie. Now try this and it will work. 2 options.

    1. a)count all not professional, or b) count all where you have data ( may be primary )
    2. a) count all blank on source or b) Count all professional
    3. a) add both counts. or b) subtract both count
    4. your formula will be something like either of these
    5. =COUNTIFS(FY:FY, "FY19", Source:Source, <> "Professional")+COUNTIFS(FY:FY, "FY19", Source:Source, "")
    6. =COUNT(Primary colum:Primary column)-COUNTIFS(FY:FY, "FY19", Source:Source, "Professional")
    7. Hit Accept if you get correct.
  • Lchicklis
    Lchicklis ✭✭✭✭
    Options

    Thanks so much Paul!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!