# 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

Laura

• ✭✭✭✭✭✭
Options

Give this a try...

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

• ✭✭✭✭✭✭
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"))

• ✭✭✭✭
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 :)

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

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

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

• ✭✭✭✭
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?

• ✭✭✭✭✭✭
Options

Give this a try...

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

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

Thanks so much Paul!

• ✭✭✭✭✭✭
Options

Happy to help! 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!