Formula Help!

Lchicklis

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

Paul Newcome

Give this a try...

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

Paul Newcome

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

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

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

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

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

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

Give this a try...

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

Shajahan Shabudeen

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

Thanks so much Paul!

Paul Newcome

Happy to help! 👍️